×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

# Contact US

Log In

#### Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
To Your Posts
• Keyword Search
• One-Click Access To Your
Favorite Forums
• Automated Signatures
On Your Posts
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

# formula help

 Forum Search FAQs Links MVPs

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

### RE: formula help

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

### RE: formula help

(OP)
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

### RE: formula help

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

### RE: formula help

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

### RE: formula help

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

Much easier.

### RE: formula help

(OP)
"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?

### RE: formula help

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

### RE: formula help

(OP)
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

### RE: formula help

(OP)
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

### RE: formula help

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.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

### Reply To This Thread

#### Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.