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

 

[AOB]

Interesting!

Something like this then?

=VLOOKUP(A2,{0,"0 - 2";3,"3 - 7";8,"8 - 15";16,"16 - 30";31,"31 - 90";91,"90+"},2,TRUE)

Seems to work and doesn't require data to be held elsewhere

How might one adapt this for negative numbers, given I wouldn't have a lower bound?

Only way I can think would be to wrap it inside an IFERROR statement (i.e. lookup fails because no such approximate matching value exists in the array)

=IFERROR(VLOOKUP(A2,{0,"0 - 2";3,"3 - 7";8,"8 - 15";16,"16 - 30";31,"31 - 90";91,"90+"},2,TRUE),"Negative")

And then handle blanks / invalid cell values with ISNUMBER :

=IF(ISNUMBER(A2),IFERROR(VLOOKUP(A2,{0,"0 - 2";3,"3 - 7";8,"8 - 15";16,"16 - 30";31,"31 - 90";91,"90+"},2,TRUE),"Negative"),"")

Appreciate it's better to maintain the array somewhere but I quite like the "self-containedness" of this...

Thanks!

 

[RoryA]

Appreciate it's better to maintain the array somewhere but I quite like the "self-containedness" of this...

You probably won't when you have to amend the bands.

 

[AOB]

Very fair point, of course, but the bands won't change as they are standardised

To be honest, even if they did, it's not that big a deal to update the formula?

But I do take your point

 

[Saba Sabaratnam]

No problem AOB,

I just implemented what Rory suggested and give the formula below.

=VLOOKUP(D2,{0,"0-2";3,"3-7";8,"8-15";16,"16-30";31,"31-90";91,"90+"},2,TRUE)

Kind regards

Saba

 

[Saba Sabaratnam]

You already had the answer