## formula help

## formula help

(OP)

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.

=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.

## RE: formula help

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

## RE: formula help

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

## RE: formula help

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

## RE: formula help

## RE: formula help

Much easier.

## RE: formula help

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?

## RE: formula help

## RE: formula help

=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

## RE: formula help

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

## RE: formula help

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.