"too much nesting"

[gss]

I'm getting an error that says "the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format".

Is there any way around this? I need the entire formula I'm entering into the cell.

Thanks

[NBVC]

What exactly is your current formula and what do you want it to do?

There are several ways around the nested if's.. but we need to know what you are looking at.

[gss]

This is what I currently have which works:

=(IF(ISERROR(B5-5>=0),"",IF(AND(D4>Cp_1,Cp_1>D4-5),Cp_1,IF(AND(D4>Cp_2,Cp_2>D4-5),Cp_2,IF(AND(D4>Cp_3,Cp_3>D4-5),Cp_3,IF(AND(D4>Cp_4,Cp_4>D4-5),Cp_4,IF(((B5-5)<>(CEILING(B5,5)-5)),FLOOR(B5,5),IF(B5-5>=0,B5-5,""))))))))

And this is what I need (I've added a bunch of if statements to the one above):

=(IF(ISERROR(B5-5>=0),"",IF(AND(D4>Cp_1,Cp_1>D4-5),Cp_1,IF(AND(D4>Cp_2,Cp_2>D4-5),Cp_2,IF(AND(D4>Cp_3,Cp_3>D4-5),Cp_3,IF(AND(D4>Cp_4,Cp_4>D4-5),Cp_4,IF(AND(D4>Hta_1,Hta_1>D4-5),Hta_1, IF(AND(D4>Hta_2,Hta_2>D4-5),Hta_2, IF(AND(D4>Hta_3,Hta_3>D4-5),Hta_3, IF(AND(D4>Hta_4,Hta_4>D4-5),Hta_4, IF(AND(D4>Hta_5,Hta_5>D4-5),Hta_5, IF(AND(D4>Htb_1,Htb_1>D4-5),Htb_1, IF(AND(D4>Htb_2,Htb_2>D4-5),Htb_2, IF(AND(D4>Htb_3,Htb_3>D4-5),Htb_3, IF(AND(D4>Htb_4,Htb_4>D4-5),Htb_4, IF(AND(D4>Htb_5,Htb_5>D4-5),Htb_5,IF(((B5-5)<>(CEILING(B5,5)-5)),FLOOR(B5,5),IF(B5-5>=0,B5-5,""))))))))

[NBVC]

Assuming you are expecting numeric results, try:

Please Login or Register  to view this content.

Basically it separates the conditions into separate IF() statements and adds the results of each together... all but 1 should result in 0... so the final result should be the result you want.

[gss]

It didn't work. It's giving me a #NAME? error.

[gss]

I may be doing something else wrong because I deleted all the additional stuff I added to the original code (from yours) and it works.

i.e.

=IF(ISERROR(B5-5>=0),0,IF(AND(D4>Cp_1,Cp_1>D4-5),Cp_1,0)+IF(AND(D4>Cp_2,Cp_2>D4-5),Cp_2,0)+IF(AND(D4>Cp_3,Cp_3>D4-5),Cp_3,0)+IF(AND(D4>Cp_4,Cp_4>D4-5),Cp_4,0)+IF(((B5-5)<>(CEILING(B5,5)-5)),FLOOR(B5,5),IF(B5-5>=0,B5-5,0)))

I'll take another look to make sure all the variables in the code are actually called out.

[gss]

I fixed my mistake and now the code works, but for some reason, where it should be giving me an output of 113, it's giving me an ouput of 223.

I'll figure out where I'm going wrong.

Thanks a lot!

[NBVC]

Is it possible that more than one of the separate IF statements will give a TRUE result? If so, then those are being added together... and also, if so, should there be more than one TRUE result?

[gss]

Most of the time (as is the case where the output should be 113 but it's giving me 223) only one of the IF statements will give a TRUE result. At most two cases will give a TRUE result. In those cases, the larger number should be used.

I believe that is the problem, the two numbers are being added. Right now (with the original code) the output is 110. I should be getting a 113. Adding those together it's giving me 223.

Can you help me fix this. I have no idea where to start.

Thanks.

[NBVC]

Can you attach the spreadsheet?

or maybe

Please Login or Register  to view this content.

[gss]

That did the trick!

Thanks!

I have one more question:

How do I make it so that every other row that has data in it is a different color?

i.e.

Say I have values in cells A1 to A34. I want rows 2,4,6,8, etc. to change to a light green color.

[NBVC]

Great!

Please start a new thread for unrelated questions... This will help to keep the forum more organized.

Also please mark this thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[gss]

I'm sorry, but I have one more problem with this code.

The output eventually hits zero. So, for example, if D34 is zero, I want the succeeding cells to be blank (in this case D35, D36, etc). But with this code, D35 is giving me an output of #VALUE!

[NBVC]

You can use an initial IF() statement to take care of that:

e.g.

Assuming formula is in Row 4:

Please Login or Register  to view this content.

[gss]

Works! Thanks!