Excel: Program more than 64 nesting

Question

Hi!

I was wondering if anyone can help me out, I've been struggling for a few days now.

According the the input on a cell for the SEX, AGE and SKINFOLD it should return a data that is either "<P5","P5-P10", "P10-P75", "P75-95", ">P95"

Basically what I did was work with IF and AND functions, but i can't think of another way to program this.

=IF(AND(B2="M",B3>=20,B3<30,B14<5.2),("<P5"),IF(AND(B2="M",B3>=20,B3<30,B14>=5.2,B14<5.8),("P5-P10"),IF(AND(B2="M",B3>=20,B3<30,B14>=5.8,B14<15.3),("P10-P75"),IF(AND(B2="M",B3>=20,B3<30,B14>=15.3,B14<24.1),("P75-P95"),IF(AND(B2="M",B3>=20,B3<30,B14>=24.1),(">P95"),IF(AND(B2="M",B3>=30,B3<40,B14<5.6),("<P5"),IF(AND(B2="M",B3>=30,B3<40,B14>=5.6,B14<6.6),("P5-P10"),IF(AND(B2="M",B3>=30,B3<40,B14>=6.6,B14<15.5),("P10-P75"),IF(AND(B2="M",B3>=30,B3<40,B14>=15.5,B14<26.7),("P75-P95"),IF(AND(B2="M",B3>=30,B3<40,B14>=26.7),(">P95"),IF(AND(B2="M",B3>=40,B3<50,B14<5.9),("<P5"),IF(AND(B2="M",B3>=40,B3<50,B14>=5.9,B14<6.9),("P5-P10"),IF(AND(B2="M",B3>=40,B3<50,B14>=6.9,B14<16.1),("P10-P75"),IF(AND(B2="M",B3>=40,B3<50,B14>=16.1,B14<25),("P75-P95"),IF(AND(B2="M",B3>=40,B3<50,B14>=25),(">P95"),IF(AND(B2="M",B3>=50,B3<60,B14<6.6),("<P5"),IF(AND(B2="M",B3>=50,B3<60,B14>=6.6,B14<7.5),("P5-P10"),IF(AND(B2="M",B3>=50,B3<60,B14>=7.5,B14<16),("P10-P75"),IF(AND(B2="M",B3>=50,B3<60,B14>=16,B14<25.2),("P75-P95"),IF(AND(B2="M",B3>=50,B3<60,B14>=25.2),(">P95"),IF(AND(B2="M",B3>=60,B3<70,B14<6.9),("<P5"),IF(AND(B2="M",B3>=60,B3<70,B14>=6.9,B14<7.7),("P5-P10"),IF(AND(B2="M",B3>=60,B3<70,B14>=7.7,B14<17.1),("P10-P75"),IF(AND(B2="M",B3>=60,B3<70,B14>=17.1,B14<25.6),("P75-P95"),IF(AND(B2="M",B3>=60,B3<70,B14>=25.6),(">P95"),IF(AND(B2="M",B3>=70,B3<80,B14<6.3),("<P5"),IF(AND(B2="M",B3>=70,B3<80,B14>=6.3,B14<7.3),("P5-P10"),IF(AND(B2="M",B3>=70,B3<80,B14>=7.3,B14<16),("P10-P75"),IF(AND(B2="M",B3>=70,B3<80,B14>=16,B14<24.4),("P75-P95"),IF(AND(B2="M",B3>=70,B3<80,B14>=24.4),(">P95"),IF(AND(B2="M",B3>=80, B14<5.4),("<P5"),IF(AND(B2="M",B3>=80,B14>=5.4,B14<6.6),("P5-P10"),IF(AND(B2="M",B3>=80,B14>=6.6,B14<13.8),("P10-P75"),IF(AND(B2="M",B3>=80,B14>=13.8,B14<22.3),("P75-P95"),IF(AND(B2="M",B3>=80,B14>G21=22.3),(">P95"),IF(AND(B2="F",B3>=20,B3<30,B14<9.9),("<P5"),IF(AND(B2="F",B3>=20,B3<30,B14>=9.9,B14<11.4),("P5-P10"),IF(AND(B2="F",B3>=20,B3<30,B14>=11.4,B14<26.0),("P10-P75"),IF(AND(B2="F",B3>=20,B3<30,B14>=26.0,B14<37.0),("P75-P95"),IF(AND(B2="F",B3>=20,B3<30,B14>=37.0),(">P95"),IF(AND(B2="F",B3>=30,B3<40,B14<10.4),("<P5"),IF(AND(B2="F",B3>=30,B3<40,B14>=10.4,B14<12.4),("P5-P10"),IF(AND(B2="F",B3>=30,B3<40,B14>=12.4,B14<31.0),("P10-P75"),IF(AND(B2="F",B3>=30,B3<40,B14>=31.0,B14<38.9),("P75-P95"),IF(AND(B2="F",B3>=30,B3<40,B14>=38.9),(">P95"),IF(AND(B2="F",B3>=40,B3<50,B14<12.0),("<P5"),IF(AND(B2="F",B3>=40,B3<50,B14>=12.0,B14<14.3),("P5-P10"),IF(AND(B2="F",B3>=40,B3<50,B14>=14.3,B14<31.2),("P10-P75"),IF(AND(B2="F",B3>=40,B3<50,B14>=31.2,B14<38.8),("P75-P95"),IF(AND(B2="F",B3>=40,B3<50,B14>=38.8),(">P95"),IF(AND(B2="F",B3>=50,B3<60,B14<14.1),("<P5"),IF(AND(B2="F",B3>=50,B3<60,B14>=14.1,B14<16.4),("P5-P10"),IF(AND(B2="F",B3>=50,B3<60,B14>=16.4,B14<32.1),("P10-P75"),IF(AND(B2="F",B3>=50,B3<60,B14>=32.1,B14<39.8),("P75-P95"),IF(AND(B2="F",B3>=50,B3<60,B14>=39.8),(">P95"),IF(AND(B2="F",B3>=60,B3<70,B14<12.4),("<P5"),IF(AND(B2="F",B3>=60,B3<70,B14>=12.4,B14<14.5),("P5-P10"),IF(AND(B2="F",B3>=60,B3<70,B14>=14.5,B14<29.7),("P10-P75"),IF(AND(B2="F",B3>=60,B3<70,B14>=29.7,B14<37.8),("P75-P95"),IF(AND(B2="F",B3>=60,B3<70,B14>=37.8),(">P95"),IF(AND(B2="F",B3>=70,B3<80,B14<11.0),("<P5"),IF(AND(B2="F",B3>=70,B3<80,B14>=11.0,B14<12.5),("P5-P10"),IF(AND(B2="F",B3>=70,B3<80,B14>=12.5,B14<27.7),("P10-P75"),IF(AND(B2="F",B3>=70,B3<80,B14>=27.7,B14<35.8),("P75-P95"),IF(AND(B2="F",B3>=70,B3<80,B14>=35.8),(">P95")"Not Enough Data")))))))))))))))))))))))))))))))))))

Thanks in advance.

Cheers

Taka

Answer 1

Hi,

Instead of nesting all these conditions, you should definitely set up a reference table and use approximate match VLOOKUPs in your formula. If you need help with that, describe your logic in words instead of pasting the whole formula.

Regards,

Takmil

Answer 2

I also just came across this article that shows how to use the CHOOSE() command to replace nested IFs.  I haven't tried to apply it to your case

Use Choose To Sum Or Average Cell Ranges And Replace Nested If Statements In Excel 2007 And Excel 2010https://turbofuture.com/computers/How-to-use-the-CHOOSE-function-in-formulas-in-Excel-2007-and-Excel-2010 December 23, 2016           Robbie C Wilson

In its simplest form, the CHOOSE function allows you to select a value from a list. So, you could, for example, have a list of seven items (such as the Seven Dwarfs) and ask the function to return the second item in the list.

CHOOSE can be used in conjunction with other functions such as SUM or AVERAGE, so I could SUM cells between a starting cell and another cell chosen by CHOOSE from a range.

At a more advanced level, it can be used to replace nested IF statements and can be configured to choose a value based on the results of a formula or the results of a Control such as a Combo Box.

We will work through an example of each scenario to illustrate the power of this useful and versatile function. The figure below shows how CHOOSE can enable you to create much simpler and easier formulas.

ET MR Function Dictionary.docx

Answer 3

Although there is much that you could improve in your formula, use the following design instead in order to avoid the limit of 64 nested functions.

You are responsible for correcting any of my typos in the tables.  I did my best.

The formula in A22 is:

=IF(OR(AND(B2<>{"m","f"}), B3="", B3<20, B14=""), "Insufficient Data",

  IF(B2="m", INDEX($G$1:$K$1, 1, MATCH(B14, INDEX($G$5:$K$11, MATCH(B3, $F$5:$F$11), 0))),

  INDEX($G$1:$K$1, 1, MATCH(B14, INDEX($G$15:$K$20, MATCH(B3, $F$15:$F$20), 0)))))

Breaking that down....

The formula returns "Insufficient Data" if any of B2, B3 or B14 is empty (or the null string), or if B2 (gender) is not "m" or "f", or if b3 (age) is less than 20.

Otherwise....

MATCH(B3, $F$5:$F$11) and MATCH(B3, $F$15:$F$20) select a row number, based on the age in B3 for each gender.

INDEX($G$5:$K$11, MATCH(B3, $F$5:$F$11), 0) and INDEX($G$15:$K$20, MATCH(B3, $F$15:$F$20), 0) select the entire age row (G5:K5, G6:K6, etc) of skinfold numbers.

MATCH(B14, INDEX($G$5:$K$11, MATCH(B3, $F$5:$F$11), 0)) and

MATCH(B14, INDEX($G$15:$K$20, MATCH(B3, $F$15:$F$20), 0)) select a column number in the selected age row, based on the skinfold in B14.

INDEX($G$1:$K$1, 1, MATCH(B14, INDEX($G$5:$K$11, MATCH(B3, $F$5:$F$11), 0))) and

INDEX($G$1:$K$1, 1, MATCH(B14, INDEX($G$15:$K$20, MATCH(B3, $F$15:$F$20), 0))) select the "P Table" string ("<P5", "P5-P10", etc) corresponding to the skinfold column in the age row.

Answer 4

Hi Taka

There will be an easier way to code this, you seem to have a lot of duplication in your formula

Can you please provide a screenshot of your sheet and an exact listing of the criteria you want form those 3 values and the expected output form that criteria

Answer 5

Ok, that is a sharp poke in the eye.  Sorry, I just don't feel like wading into it ...

64 levels is the nested if limit introduced in 2007. That still applies:

@ Excel specifications and limitshttps://support.office.com/en-ie/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Newer_versions****Worksheet and workbook specifications and limits

Calculation specifications and limits

Charting specifications and limits

PivotTable and PivotChart report specifications and limits

Workbooks with the "Allow changes by more than one user..." setting enabled (No TABLES!)

Your other alternative is using Select CASE in VBA. No limit there.

But your other option is to look carefully at the logic and see if you can group repeated logic to reduce number of Ifs.

Try writing out the logic in english to make it easier to find repetitions