formula help

[grunt58]

Can anyone tell me whats wrong with this formula? It doesn't like the AND in the last IF AND statement. Is there to many statements? If I get rid of any of the other statements it works so thats why I believe it's a #'s thing.

=IF($B$41="N/A","N/A",IF(AND($B$40=1,$B$18=60,$B$4<5),($B$41-(145+145))/ROUNDUP(($B$41-(145+145))/400,0),IF(AND($B$40=1,$B$18=60,$B$4>4),($B$41-(162.5+162.5))/ROUNDUP(($B$41-(162.5+162.5))/400,0),IF(AND($B$40=1,$B$18=100,$B$4<5),($B$41-(157.5+157.5))/ROUNDUP(($B$41-(157.5+157.5))/400,0),IF(AND($B$40=1,$B$18=100,$B$4>4),($B$41-(172.5+172.5))/ROUNDUP(($B$41-(172.5+172.5))/400,0),IF(AND($B$40>1,$B$18=100,$B$4<5),($B$41-(30+157.5))/ROUNDUP(($B$41-(30+157.5))/400,0),IF(AND($B$40>1,$B$18=100,$B$4>4),(($B$41-(30+172.5))/ROUNDUP(($B$41-(30+172.5))/400,0),IF(AND($B$40>1,$B$18=100,$B$4<5),($B$41-(30+162.5))/ROUNDUP(($B$41-(30+162.5))/400,0)))))))))


Thanks in advance.

 

[carterinms]

Sounds like there are too many (8) statements. This was in the excel help:

"Nest no more than seven functions You can enter, or nest, no more than seven levels of functions within a function."

 

[grunt58]

Yes I saw that in the help but I thought I was nesting only 7 statements. Does the intial statment =IF($B$41="N/A","N/A" count towards the nesting #?

Grant
Applications Engineer
SW2005 SP 3.1
IBM InteliStation Pro M
P4 3.4 GHz, 2GB RAM
XP Pro SP2.0
NIVIDA Quadro FX 1100

 

[carterinms]

I started playing with the formula to see if it could be simplified, and I notice a problem with your if-statements. In your third if-statement, do you mean "$B$4>=5"? I'm not sure why you have >4, since anything less than 5 will be skipped. But, if you mean >=5, the >4 will do the same thing so maybe there isn't a problem.

What about eliminating your last IF? You should have a statement like: IF(rule, true, false), but I don't see anything for the false condition in your last if-statement. If all the possible variations were taken care of in the previous if-statements, than by default the final "false" equation should have to be "AND($B$40>1,$B$18=100,$B$4<5)", thus you don't need an if.

The only time I would use an if for the final statement is if I wanted to introduce an error message: IF(AND(A=1, B=1),3,IF(AND(A=1, B=2),2,IF(AND(A=2, B=1),3,IF(AND(A=2, B=2),4,"ERROR!")))). If A and B could only be 1 or 2, then I wouldn't need the final if and I could simplify the formula to: IF(AND(A=1, B=1),3,IF(AND(A=1, B=2),2,IF(AND(A=2, B=1),3,4))) which reduces the number of statements.

-Erica

 

[carterinms]

Each IF counts as a statement, so your initial statement counts towards the nesting # limit.

 

[MintJulep]

Write a custom user function with VBA using select...case statements.

Much easier.

 

[grunt58]

"But, if you mean >=5, the >4 will do the same thing so maybe there isn't a problem" i find it easier that way.

Unfortuatly I need my last statement + 1 more then i will end with if false put "N/A".

How can I use/reference another cell to extend my # of statements?

 

[carterinms]

In your 7th IF statement, reference the other cell as your false condition and continue the if's in that cell.

 

[grunt58]

I don't quit understand.

=IF($B$41="N/A","N/A",IF(AND($B$40=1,$B$18=60,$B$4<5),($B$41-(145+145))/ROUNDUP(($B$41-(145+145))/400,0),IF(AND($B$40=1,$B$18=60,$B$4>4),($B$41-(162.5+162.5))/ROUNDUP(($B$41-(162.5+162.5))/400,0),IF(AND($B$40=1,$B$18=100,$B$4<5),($B$41-(157.5+157.5))/ROUNDUP(($B$41-(157.5+157.5))/400,0),IF(AND($B$40=1,$B$18=100,$B$4>4),($B$41-(172.5+172.5))/ROUNDUP(($B$41-(172.5+172.5))/400,0),IF(AND($B$40>1,$B$18=100,$B$4<5),($B$41-(30+157.5))/ROUNDUP(($B$41-(30+157.5))/400,0),IF(AND($B$40>1,$B$18=100,$B$4>4),(($B$41-(30+172.5))/ROUNDUP(($B$41-(30+172.5))/400,0),IF(AND($B$40>1,$B$18=100,$B$4<5),($B$41-(30+162.5))/ROUNDUP(($B$41-(30+162.5))/400,0,$C$44)))))))))

or

=IF($B$41="N/A","N/A",IF(AND($B$40=1,$B$18=60,$B$4<5),($B$41-(145+145))/ROUNDUP(($B$41-(145+145))/400,0),IF(AND($B$40=1,$B$18=60,$B$4>4),($B$41-(162.5+162.5))/ROUNDUP(($B$41-(162.5+162.5))/400,0),IF(AND($B$40=1,$B$18=100,$B$4<5),($B$41-(157.5+157.5))/ROUNDUP(($B$41-(157.5+157.5))/400,0),IF(AND($B$40=1,$B$18=100,$B$4>4),($B$41-(172.5+172.5))/ROUNDUP(($B$41-(172.5+172.5))/400,0),IF(AND($B$40>1,$B$18=100,$B$4<5),($B$41-(30+157.5))/ROUNDUP(($B$41-(30+157.5))/400,0),IF(AND($B$40>1,$B$18=100,$B$4>4),(($B$41-(30+172.5))/ROUNDUP(($B$41-(30+172.5))/400,0),$C$44)))))))))

or neither and I'm not even close.

thanks for all your help Erica

Grant

 

[grunt58]

I figured it out. Guess I should read more carefully!

Again thanks for your help Erica.

Grant

Grant
Applications Engineer
SW2005 SP 3.1
IBM InteliStation Pro M
P4 3.4 GHz, 2GB RAM
XP Pro SP2.0
NIVIDA Quadro FX 1100

 

[Mutt]

Just as an aside -
I find with these multi conditional formulae it is far easier to debug if you quickly write a VBasic defined function to do the job.........plus no limit to number of levels.