×
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

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.

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



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close