Nested IF Functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kelly - 07 Mar 2008 22:58 GMT

All three cells listed in the formula below are dates. If there is data in
cell R9, that cell should be calculated as below. If 01/00/00 is listed in
R9, the formula should calculate R4 in the same manner. I have started with
the following formula, but obviously have not incorporated in R4. Can anyone
help?

=IF($R$9-A73>500,0,IF($R$9-A73<=0,0,IF($R$9-A73>0,SUM($R$9-A73))))

Reply to this Message

Tyro - 07 Mar 2008 23:25 GMT

Your formula makes no sense to me. What is 01/00/00? The first of 00 of
year 00? You refer to "three cells" yet you show only two $R$9 and A73 in
your formula.
R4 is dependent on R9 and its 01/00/00. Please explain in simple English
what you're trying to accomplish. Your formula appears to be saying that if
the difference in days is greater than 500, 0 is the answer, and if not, if
the difference in days is zero or negative, then 0 is the answer, then if
not, if the difference in days is greater than 0, "SUM($R$9-A73)" which
could be simply expressed as $R$9-A73. There is nothing to SUM. Your
formula can be reduced to =IF(OR($R$9-A73>500,$R$9-A73<=0),0,$R$9-A73)

Tyro

> All three cells listed in the formula below are dates. If there is data
> in
[quoted text clipped - 7 lines]
>
> =IF($R$9-A73>500,0,IF($R$9-A73<=0,0,IF($R$9-A73>0,SUM($R$9-A73))))

Reply to this Message

Kelly - 07 Mar 2008 23:46 GMT

the 01/00/00 is a blank date (R9); it is linked to another another
spreadsheet and waiting for an actual date to be entered once a deal is
closed. The third cell is R4. R4 is a "Current Through" date and R9 is an
"Acceptance" Date. There are many times when the Acceptance Date (R9) is not
included when I am processing commissions so I need to use the Current
Through Date (R4) to run the calculation. In my formula below I am only able
to use the Acceptance Date. Hopefully that makes more sense.

> Your formula makes no sense to me. What is 01/00/00? The first of 00 of
> year 00? You refer to "three cells" yet you show only two $R$9 and A73 in
[quoted text clipped - 20 lines]
> >
> > =IF($R$9-A73>500,0,IF($R$9-A73<=0,0,IF($R$9-A73>0,SUM($R$9-A73))))

Reply to this Message

Tyro - 08 Mar 2008 00:21 GMT

Is 01/00/00 an Excel date or text? Blank dates are usually just blank cells
which are interpreted as 0, Jan 0, 1900

Tyro

> the 01/00/00 is a blank date (R9); it is linked to another another
> spreadsheet and waiting for an actual date to be entered once a deal is
[quoted text clipped - 35 lines]
>> >
>> > =IF($R$9-A73>500,0,IF($R$9-A73<=0,0,IF($R$9-A73>0,SUM($R$9-A73))))

Reply to this Message

Kelly - 10 Mar 2008 15:39 GMT

The date is coming from "B" spreadsheet, which someone hand typed in as
01/00/00 and formatted as a date. When I look at the cell where the date is
(spreadsheet "A"), it is a link to the "B" spreadsheet and has been formatted
as a date.

> Is 01/00/00 an Excel date or text? Blank dates are usually just blank cells
> which are interpreted as 0, Jan 0, 1900
[quoted text clipped - 40 lines]
> >> >
> >> > =IF($R$9-A73>500,0,IF($R$9-A73<=0,0,IF($R$9-A73>0,SUM($R$9-A73))))

Reply to this Message