• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel doesn't allow more than 64 levels of nesting, can someone help me simplify this formula?thanks

Status
Not open for further replies.
A

Amaia

New Member
=IF(AND(H40<=4,H40>=1),H40-H42,


IF(AND(H40<=8,H40>=5,H41<=4),4-H41,

IF(AND(H40<=8,H40>=5,H41>=5,H41<9),H40-H41,


IF(AND(H40<=12,H40>=9,H41<=4),4-H41,

IF(AND(H40<=12,H40>=9,H41>=5,H41<9),8-H41,

IF(AND(H40<=12,H40>=9,H41>=9,H41<13),H40-H41,


IF(AND(H40<=16,H40>=13,H41<=4),4-H41,

IF(AND(H40<=16,H40>=13,H41>=5,H41<9),8-H41,

IF(AND(H40<=16,H40>=13,H41>=9,H41<13),12-H41,

IF(AND(H40<=16,H40>=13,H41>=13,H41<17),H40-H41,


IF(AND(H40<=20,H40>=17,H41<=4),4-H41,

IF(AND(H40<=20,H40>=17,H41>=5,H41<9),8-H41,

IF(AND(H40<=20,H40>=17,H41>=9,H41<13),12-H41,

IF(AND(H40<=20,H40>=17,H41>=13,H41<17),16-H41,

IF(AND(H40<=20,H40>=17,H41>=17,H41<21),H40-H41,


IF(AND(H40<=24,H40>=21,H41<=4),4-H41,

IF(AND(H40<=24,H40>=21,H41>=5,H41<9),8-H41,

IF(AND(H40<=24,H40>=21,H41>=9,H41<13),12-H41,

IF(AND(H40<=24,H40>=21,H41>=13,H41<17),16-H41,

IF(AND(H40<=24,H40>=21,H41>=17,H41<21),20-H41,

IF(AND(H40<=24,H40>=21,H41>=21,H41<25),H40-H41,


IF(AND(H40<=28,H40>=25,H41<=4),4-H41,

IF(AND(H40<=28,H40>=25,H41>=5,H41<9),8-H41,

IF(AND(H40<=28,H40>=25,H41>=9,H41<13),12-H41,

IF(AND(H40<=28,H40>=25,H41>=13,H41<17),16-H41,

IF(AND(H40<=28,H40>=25,H41>=17,H41<21),20-H41,

IF(AND(H40<=28,H40>=25,H41>=21,H41<25),24-H41,

IF(AND(H40<=28,H40>=25,H41>=25,H41<29),H40-H41,


IF(AND(H40<=32,H40>=29,H41<=4),4-H41,

IF(AND(H40<=32,H40>=29,H41>=5,H41<9),8-H41,

IF(AND(H40<=32,H40>=29,H41>=9,H41<13),12-H41,

IF(AND(H40<=32,H40>=29,H41>=13,H41<17),16-H41,

IF(AND(H40<=32,H40>=29,H41>=17,H41<21),20-H41,

IF(AND(H40<=32,H40>=29,H41>=21,H41<25),24-H41,

IF(AND(H40<=32,H40>=29,H41>=25,H41<29),28-H41,

IF(AND(H40<=32,H40>=29,H41>=29,H41<33),H40-H41,


IF(AND(H40<=36,H40>=33,H41<=4),4-H41,

IF(AND(H40<=36,H40>=33,H41>=5,H41<9),8-H41,

IF(AND(H40<=36,H40>=33,H41>=9,H41<13),12-H41,

IF(AND(H40<=36,H40>=33,H41>=13,H41<17),16-H41,

IF(AND(H40<=36,H40>=33,H41>=17,H41<21),20-H41,

IF(AND(H40<=36,H40>=33,H41>=21,H41<25),24-H41,

IF(AND(H40<=36,H40>=33,H41>=25,H41<29),28-H41,

IF(AND(H40<=36,H40>=33,H41>=29,H41<33),32-H41,

IF(AND(H40<=36,H40>=33,H41>=33,H41<37),H40-H41,


IF(AND(H40<=40,H40>=37,H41<=4),4-H41,

IF(AND(H40<=40,H40>=37,H41>=5,H41<9),8-H41,

IF(AND(H40<=40,H40>=37,H41>=9,H41<13),12-H41,

IF(AND(H40<=40,H40>=37,H41>=13,H41<17),16-H41,

IF(AND(H40<=40,H40>=37,H41>=17,H41<21),20-H41,

IF(AND(H40<=40,H40>=37,H41>=21,H41<25),24-H41,

IF(AND(H40<=40,H40>=37,H41>=25,H41<29),28-H41,

IF(AND(H40<=40,H40>=37,H41>=29,H41<33),32-H41,

IF(AND(H40<=40,H40>=37,H41>=33,H41<37),36-H41,

IF(AND(H40<=40,H40>=37,H41>=37,H41<41),H40-H41,


IF(AND(H40<=44,H40>=41,H41<=4),4-H41,

IF(AND(H40<=44,H40>=41,H41>=5,H41<9),8-H41,

IF(AND(H40<=44,H40>=41,H41>=9,H41<13),12-H41,

IF(AND(H40<=44,H40>=41,H41>=13,H41<17),16-H41,

IF(AND(H40<=44,H40>=41,H41>=17,H41<21),20-H41,

IF(AND(H40<=44,H40>=41,H41>=21,H41<25),24-H41,

IF(AND(H40<=44,H40>=41,H41>=25,H41<29),28-H41,

IF(AND(H40<=44,H40>=41,H41>=29,H41<33),32-H41,

IF(AND(H40<=44,H40>=41,H41>=33,H41<37),36-H41,

IF(AND(H40<=44,H40>=41,H41>=37,H41<41),40-H41,

IF(AND(H40<=44,H40>=41,H41>=41,H41<45),H40-H41,


IF(AND(H40<=48,H40>=45,H41<=4),4-H41,

IF(AND(H40<=48,H40>=45,H41>=5,H41<9),8-H41,

IF(AND(H40<=48,H40>=45,H41>=9,H41<13),12-H41,

IF(AND(H40<=48,H40>=45,H41>=13,H41<17),16-H41,

IF(AND(H40<=48,H40>=45,H41>=17,H41<21),20-H41,

IF(AND(H40<=48,H40>=45,H41>=21,H41<25),24-H41,

IF(AND(H40<=48,H40>=45,H41>=25,H41<29),28-H41,

IF(AND(H40<=48,H40>=45,H41>=29,H41<33),32-H41,

IF(AND(H40<=48,H40>=45,H41>=33,H41<37),36-H41,

IF(AND(H40<=48,H40>=45,H41>=37,H41<41),40-H41,

IF(AND(H40<=48,H40>=45,H41>=41,H41<45),44-H41,

IF(AND(H40<=48,H40>=45,H41>=45,H41<49),H40-H41,


IF(AND(H40<=52,H40>=49,H41<=4),4-H41,

IF(AND(H40<=52,H40>=49,H41>=5,H41<9),8-H41,

IF(AND(H40<=52,H40>=49,H41>=9,H41<13),12-H41,

IF(AND(H40<=52,H40>=49,H41>=13,H41<17),16-H41,

IF(AND(H40<=52,H40>=49,H41>=17,H41<21),20-H41,

IF(AND(H40<=52,H40>=49,H41>=21,H41<25),24-H41,

IF(AND(H40<=52,H40>=49,H41>=25,H41<29),28-H41,

IF(AND(H40<=52,H40>=49,H41>=29,H41<33),32-H41,

IF(AND(H40<=52,H40>=49,H41>=33,H41<37),36-H41,

IF(AND(H40<=52,H40>=49,H41>=37,H41<41),40-H41,

IF(AND(H40<=52,H40>=49,H41>=41,H41<45),44-H41,

IF(AND(H40<=52,H40>=49,H41>=45,H41<49),48-H41,

IF(AND(H40<=52,H40>=49,H41>=49,H41<53),H40-H41,

))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
 
Hi ,

I don't think anyone would be interested to go through all of these IF statements to decipher the logic you want implemented.

If you can present a table which details this logic , it would be a simple matter to develop the Excel formula.

Please create a table of these two cells H40 and H41 , and list the various values these two cells can take , and for each combination what should be the desired output , and upload this workbook in this same thread.

Narayan
 
Hi Narayan,

Thank you for your help and advice. I didn't want to reqest a full rewriting. The formula works as it is until the 64th nesting. Then it cant work as it exceeds the Excel limit.
I didn't upload the file as Ecel states there is an erorr because of this limit.
Logic is as follows: There are only 2 variables H40 and H41, except for the 1st IF, and H41 can never exceed H40's value. Both values cannot exceed 52.

That is pretty much it. I can hardly be clearer, sorry for this.

Thanks for your help anyway :)
 
Amaia: the IF formula is incredibly inefficient in cases like this, even if you had below 64 levels of nesting so that you could actually enter it.

What we need you to do is to describe the logic of what you are trying to achieve in words, and we can give you a formula that will be hundreds of times more efficient than an IF-based formula.

We can probably deduce the logic from your formula if we try hard, but the ideal situation is for the original poster to tell us in words what is required so we don't have to think too hard, and can concentrate on giving a solution rather than having to expend lots of energy trying to deduce the problem.
 
Okay, looking at the logic in your formula, I suspect that all you actually need is one very simple formula:
=CEILING(H21,4)-H21

This would need you to put a data validation condition on H21 with the following condition:
=H21<=H20

...so that the user could never enter something in H21 that exceeds the value in H20:
DV condition.png

You could also add a custom error message so that the user was told what to do if they tried to enter a value in H21 that exceeded H20:

DV Error.png

...which would display this:

DV error display.png
 
Whoops, that above formula doesn't quite do it, but it is close. You can use one simple formula instead of those 64+ nested IFs. I'm pretty sure I'll have the correct answer shortly.
 
Here's the corrected formula:
=IF(H40<=4,H40-H42,IF(CEILING(H40,4)-CEILING(H41,4)<3,H40-H41,CEILING(H41,4)-H41))

All you need to do in addition to this is use Data Validation as outlined above to ensure whatever the user enters in cell H41 doesn't exceed cell H40's value, and also that both Cell H41 and H40 are under your threshold of 52.
 
jeffreyweir said:
Here's the corrected formula:
=IF(H40<=4,H40-H42,IF(CEILING(H40,4)-CEILING(H41,4)<3,H40-H41,CEILING(H41,4)-H41))

All you need to do in addition to this is use Data Validation as outlined above to ensure whatever the user enters in cell H41 doesn't exceed cell H40's value, and also that both Cell H41 and H40 are under your threshold of 52.
Click to expand...

If you've correctly divined his needs, you deserve the Excel Medal for patience and a Bar for reducing a formula by the greatest amount ever!
Simplify, Simplify, Simplify - a wonderful mantra for Excel-ers
 
Thanks Dave...it took quite a while for me to work out, but this is such a great example of the dangers of IF (and the benefits of using something else) that I just couldn't help but spend the time.
 
It's worth noting that the first part of the above formula is just to handle the special case Amaia mentions in regards to the very first IF in there. So the base formula is actually just this:
=IF(CEILING(H40,4)-CEILING(H41,4)<3,H40-H41,CEILING(H41,4)-H41)
 
Greetings
My requirements are:
formula cell is G16
I'm trying to assign auto numbering according to discipline cell(H16) and building cell(J16) criteria. It is working but exceeding 64 nesting. Need to shorten this formula. Need support. I attached the sheet

=IF(H16="","",IF(AND(J16="1BS-02",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS-04"),IF(AND(J16="1BN-02",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN-04"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"))))))))))))))))))))))))))))))))))))))))))))))))))))))))
 

Attachments

  • RQI.xlsx
    21.1 KB · Views: 1
Hussain_0929
You should open a new thread
as You've read from Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
This thread is few years old and now ... closed.
 
Status
Not open for further replies.
Back
Top