evaluate
in [Functions]
|
Prev: Tables in excel- bringing down formulas for new records.
Next: How to insert maths symbols into an excell function?
From: Rick Rothstein on 16 May 2010 14:04 No, I don't see how that could ever be True. -- Rick (MVP - Excel) "afdmello" <afdmello(a)hotmail.com> wrote in message news:eXrWFES9KHA.3840(a)TK2MSFTNGP02.phx.gbl... > I saw a worksheet for training programs in which this formula is written > =IF(C5<1/1/1902,0,IF(C6>EDATE(C6,12),0,1)) > > can the function C6>EDATE(C6,12) can ever be true as the logic is refering > to the same cell > > afd >
From: Ron Rosenfeld on 16 May 2010 15:12 On Sun, 16 May 2010 20:55:47 +0300, "afdmello" <afdmello(a)hotmail.com> wrote: >I saw a worksheet for training programs in which this formula is written >=IF(C5<1/1/1902,0,IF(C6>EDATE(C6,12),0,1)) > >can the function C6>EDATE(C6,12) can ever be true as the logic is refering >to the same cell > >afd > In addition to agreeing with Rick's comment, I would also point out that the first conditional does not compare C5 with a date, but rather compares C5 with 0.000525762 (which is 1 divided by 1902) --ron
From: T. Valko on 16 May 2010 16:11
>a worksheet for training programs in which this formula is written >=IF(C5<1/1/1902,0,IF(C6>EDATE(C6,12),0,1)) If it's for training purposes then maybe it's being used as an example of how not to write a formula! IF(C6>EDATE(C6,12) will *never* be TRUE. There are only 2 possible results, either FALSE or an error. Here's what that is saying in plain English... If the value of cell C5 is less than 1 divided by 1 divided by 1902 then return 0. If the value of cell C5 is not less than 1 divided by 1 divided by 1902 then test the value of cell C6 to see if it is greater than the date of C6 12 months later. If C6 is greater than the date of C6 12 months later return 0 otherwise return 1. 1 divided by 1 divided by 1902 = 0.000525762355415352 It would be technically possible for C5 to be less than 0.000525762355415352. That portion of the formula should be written like this: =IF(C5<DATE(1902,1,1),0... Or the preferred method, use a cell to hold the date then refer to that cell: C4 = 1/1/1902 =IF(C5<C4,0... Let's assume C6 holds the date 1/1/2010. EDATE(C6,12) = 1/1/2011 So: 1/1/2010 > 1/1/2011 is not possible and will never be TRUE. It's hard to say what was meant by: IF(C6>EDATE(C6,12)... -- Biff Microsoft Excel MVP "afdmello" <afdmello(a)hotmail.com> wrote in message news:eXrWFES9KHA.3840(a)TK2MSFTNGP02.phx.gbl... >I saw a worksheet for training programs in which this formula is written > =IF(C5<1/1/1902,0,IF(C6>EDATE(C6,12),0,1)) > > can the function C6>EDATE(C6,12) can ever be true as the logic is refering > to the same cell > > afd > |