Alternative to nested IF structure (XL2013)

[AOB]

Hi all,

I need a formula to "bucket" numerical values into groups / categories

Something along the following lines :

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]Age
[/TD]
[TD]Age Category
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0 - 2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]0 - 2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3 - 7
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3 - 7
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]8 - 15
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]8 - 15
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]16 - 30
[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]31 - 90
[/TD]
[/TR]
[TR]
[TD]106
[/TD]
[TD]90+
[/TD]
[/TR]
</tbody>[/TABLE]

















I can do this with a simple nested IF statement á la :

=IF(A2<3,"0 - 2",IF(A2<8,"3 - 7",IF(A2<16,"8 - 15",IF(A2<31,"16 - 30",IF(A2<91,"31 - 90","90+")))))

But I'm curious to see if there's a better, more efficient, method. I don't really like nested IFs; for a start, there's a limit on the number of IFs you can nest (is it 7?) but also they can be difficult to read and, well, I'd just like to learn a better way.

My initial thoughts were something along the lines of the SWITCH statement in Access, where you evaluate an arbitrary series of statements and return the output for the first statement which returns TRUE - which would be perfect here - but there doesn't seem to be an equivalent in Excel?

There's CHOOSE but with that, one would have to specify the return value for each possible input (i.e. 1, 2, 3,.....,X) rather than value ranges

Any other suggestions?

Thanks!

Al

 

[Saba Sabaratnam]

Hi

Please find alternative formula to solve your problem and you can download the file from the following link:

https://sabercathost.com/9pCV/Alternative_to_nested_IF_structure_(XL2013).xlsx

Let me know if you have questions.

Kind regards

Saba

 

[Saba Sabaratnam]

Correct link is given below.

https://sabercathost.com/9pD0/Alternative_to_nested_IF_structure_(XL2013).xlsx

 

[AOB]

Thanks Saba - I can't open that link though - can you tell me what alternative native function you would recommend?

 

[Peter_SSs]

Hi

Please find alternative formula to solve your problem and you can download the file from the following link:

https://sabercathost.com/9pCV/Alternative_to_nested_IF_structure_(XL2013).xlsx

Let me know if you have questions.

Kind regards

Saba

Saba, please read and follow rule #4 (particularly the last two paragraphs) of the Forum Rules.

 

[Saba Sabaratnam]

No problem

Organise the data as given below in A2:B7 (two columns)

[TABLE="width: 159"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Lookup table[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0-2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3-7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8-15[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]16-30[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]31-90[/TD]
[/TR]
[TR]
[TD]91[/TD]
[TD]90+[/TD]
[/TR]
</tbody>[/TABLE]


Then

Use the following formula for approximate lookup (note TRUE is used instead of usual FALSE)

=VLOOKUP(D2,$A$2:$B$7,2,TRUE)

The following table starts from D2 as shown below and the formula is entered into E2 and copy it down.

[TABLE="width: 122"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Age[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0-2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0-2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3-7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3-7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8-15[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]8-15[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]16-30[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]31-90[/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]90+[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if you have questions.

 

[Saba Sabaratnam]

Thank you Peter,

I am new here and just wanted to give the solution instead of writing it up.

I will follow the rule from now on.

Kind regards

Saba

 

[Peter_SSs]

I am new here ...

I will follow the rule from now on.

Thanks.

You might find this useful instead: Attachments

 

[AOB]

Thanks for the suggestion Saba - it's certainly more scalable than nested IFs although I'd prefer not to have to introduce new data to support the formula. I was more curious to see if it could be done via native Excel functions alone. But thanks for the suggestion!

 

[RoryA]

You can put the lookup table into the formula as a hardcoded array, but it's better practice to use a separate range for that data as it's much easier to maintain.