IF NESTED Question

[Guest]

Here is the formula currently:
=IF(AND($R4="NONE",$S4="NONE"),"NONE", IF(($E4-DAY($E4)+1)=AB$3,($U4+$V4)/6,
IF(($F4-DAY($F4)+1)=AB$3,($W4+$X4)/12,
IF(OR(($R4-DAY($R4)+1)=AB$3,($S4-DAY($S4)+1)=AB$3),"END", IF(AA4>0,AA4,0)))))

Where: If R4 and S4 = NONE, then put "none" in AB4; if date in E4 (default
to beg of month) = AB3 (also a date), then calculate; if date in F4 = AB3,
then calculate; if date in R4 = AB3 OR if date in S4 = AB3, then put "end" in
AB4; if AA4>0, then put $ amount that is in AA4 in cell AB4; if all false,
put $0 in AB4.

Formula seems to work - however, it is not putting the word "end" when R4 or
S4 = AB3, it simply defaults to $0. Have I forgotten a step in the formula?
Or is there a trick I don't know about?

Please HELP! I've been working on this formula for days now!

Thanks in advance,

Heather

 

[Guest]

Hi
You seem to have a surfeit of brackets, which may be confusing you.
Try this:
=IF(AND($R4="NONE",$S4="NONE"),"NONE", IF($E4-DAY($E4)+1=AB$3,($U4+$V4)/6,
IF($F4-DAY($F4)+1=AB$3,($W4+$X4)/12,
IF(OR($R4-DAY($R4)+1=AB$3,$S4-DAY($S4)+1=AB$3),"END", IF(AA4>0,AA4,0))))
It may also be worth selecting the bit you believe to be true:
OR($R4-DAY($R4)+1=AB$3,$S4-DAY($S4)+1=AB$3)
in the formula bar and hitting F9 to see what Excel calculates it to be
(TRUE or FALSE).
Btw, I've not checked the above formula, so backup before you use it!

 

[Guest]

Andy,
Thank you for your reply. I tried it without so many brackets, it still
does not work. hmmm?
I also tried to select the piece I believe to be true, F9 and Excel simply
responds with "Your Formula is missing a parenthesis --)" I even tried the
F9 trick on another portion of my formula where I had all the brackets and it
said the same thing.

Got any ideas? It's a mystery to me!

Thanks!
Heather

 

[Guest]

GOT IT!!!

=IF(AND($R4,$S4="NONE"),"NONE", IF($E4-DAY($E4)+1=AB$3,($U4+$V4)/6,
IF($F4-DAY($F4)+1=AB$3,($W4+$X4)/12,
IF(OR($R4-DAY($R4)+1,$S4-DAY($S4)+1=AB$3),"END", IF(AA4>0,AA4,0)))))

WOW! It even made the thing shorter!!
Thanks!
Hope this helps someone else out too!
Heather

 

[Guest]

Hi

Glad to hear you're sorted!

--
Andy.

GOT IT!!!

=IF(AND($R4,$S4="NONE"),"NONE", IF($E4-DAY($E4)+1=AB$3,($U4+$V4)/6,
IF($F4-DAY($F4)+1=AB$3,($W4+$X4)/12,
IF(OR($R4-DAY($R4)+1,$S4-DAY($S4)+1=AB$3),"END", IF(AA4>0,AA4,0)))))

WOW! It even made the thing shorter!!
Thanks!
Hope this helps someone else out too!
Heather