| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
IF NESTED Question
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(AA40,AA4,0))))) Whe 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 AA40, 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 |
|
#2
|
|||
|
|||
|
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(AA40,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! -- Andy. "Heather" wrote in message ... 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(AA40,AA4,0))))) Whe 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 AA40, 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 |
|
#3
|
|||
|
|||
|
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 "Andy B" wrote: 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(AA40,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! -- Andy. "Heather" wrote in message ... 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(AA40,AA4,0))))) Whe 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 AA40, 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 |
|
#4
|
|||
|
|||
|
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(AA40,AA4,0))))) WOW! It even made the thing shorter!! Thanks! Hope this helps someone else out too! Heather "Heather" wrote: 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 "Andy B" wrote: 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(AA40,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! -- Andy. "Heather" wrote in message ... 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(AA40,AA4,0))))) Whe 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 AA40, 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 |
|
#5
|
|||
|
|||
|
Hi
Glad to hear you're sorted! -- Andy. "Heather" wrote in message ... 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(AA40,AA4,0))))) WOW! It even made the thing shorter!! Thanks! Hope this helps someone else out too! Heather "Heather" wrote: 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 "Andy B" wrote: 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(AA40,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! -- Andy. "Heather" wrote in message ... 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(AA40,AA4,0))))) Whe 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 AA40, 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
| Nested IF statements | Excel Worksheet Functions | |||
| An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
| €śUse of more then 7 nested if statement€ť | Excel Worksheet Functions | |||
| 7+ nested if statement? | Excel Worksheet Functions | |||
Linear Mode
