Workaround for nesting more than 7 statements

Guys, I am brand new here and with a mere average knowledge of excel and I originally had a huge formula with multiple nested statements, but because Excel cannot nest more than 7, I needed to come up with a workaround. The formula below is ideally what I required:

Please Login or Register  to view this content.

Now I was able to get some fantastic help on this to simplify to the following formula which is in fact working perfectly:

Please Login or Register  to view this content.

The problem is that I am having difficulty understanding in layman's terms what the formula actually means and although its working perfectly, I am not confident with it and I really need to know the workings behind it. Unfortunately the person who assisted me with this so far is not available at the moment, so I'm throwing it out to you guys to see if anyone can shed any light on it. I would be so so grateful if someone could guide me on it.

My interpretation of it so far:

Vlookup the value in the table, when choosing column to bring back do not look past column 10, do not look before column 2, instead look for column 2 + rounded up figure (0 decimal places) for the max. of ..... here's where I hit a brick wall. And I can't seem to figure out the -23 and the /12 parts.

Also I'm wondering if I could use the Roundup formula as opposed to the Ceiling one?

Big thanks in advance for any assistance or feedback you can give.

http://www.cpearson.com/excel/nested.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"campka" <[email protected]> wrote in message
news:[email protected]...
|
| Guys, I am brand new here and with a mere average knowledge of excel and
| I originally had a huge formula with multiple nested statements, but
| because Excel cannot nest more than 7, I needed to come up with a
| workaround. The formula below is ideally what I required:
|
|
| Code:
| --------------------
|
=(If(ISERROR((If($K65<=23,VLOOKUP($M65,Detail!$O$5:$X$26,2,0),If($K65<=35,VLOOKUP($M65,Detail!$O$5:$X$26,3,0),If($K65<=47,VLOOKUP($M65,Detail!$O$5:$X$26,4,0),If($K65<=59,VLOOKUP($M65,Detail!$O$5:$X$26,5,0),If($K65<=71,VLOOKUP($M65,Detail!$O$5:$X$26,6,0),If($K65<=82,VLOOKUP($M65,Detail!$O$5:$X$26,7,0),If($K65<=94,VLOOKUP($M65,Detail!$O$5:$X$26,8,0),If($K65<=106,VLOOKUP($M65,Detail!$O$5:$X$26,9,0),If($K65<=118,VLOOKUP($M65,Detail!$O$5:$X$26,10,0)))))))))))))),0,(If($K65<=23,VLOOKUP($M65,Detail!$O$5:$X$26,2,0),If($K65<=35,VLOOKUP($M65,Detail!$O$5:$X$26,3,0),If($K65<=47,VLOOKUP($M65,Detail!$O$5:$X$26,4,0),If($K65<=59,VLOOKUP($M65,Detail!$O$5:$X$26,5,0),If($K65<=71,VLOOKUP($M65,Detail!$O$5:$X$26,6,0),If($K65<=82,VLOOKUP($M65,Detail!$O$5:$X$26,7,0,If($K65<=94,VLOOKUP($M65,Detail!$O$5:$X$26,8,0),If($K65<=106,VLOOKUP($M65,Detail!$O$5:$X$26,9,0),If($K65<=118,VLOOKUP($M65,Detail!$O$5:$X$26,10,0))))))))))))
| --------------------
|
|
|
| Now I was able to get some fantastic help on this to simplify to the
| following formula which is in fact working perfectly:
|
|
| Code:
| --------------------
|
=IF(ISERROR(VLOOKUP($M65,Detail!$O$5:$X$26,MIN(10,(MAX(2,2+CEILING(MAX(0,(K65+(K65>82)-23))/12,1)))),0)),0,VLOOKUP($M65,Detail!$O$5:$X$26,MIN(10,(MAX(2,2+CEILING(MAX(0,(K65+(K65>82)-23))/12,1)))),0))
| --------------------
|
|
| The problem is that I am having difficulty understanding in layman's
| terms what the formula actually means and although its working
| perfectly, I am not confident with it and I really need to know the
| workings behind it. Unfortunately the person who assisted me with this
| so far is not available at the moment, so I'm throwing it out to you
| guys to see if anyone can shed any light on it. I would be so so
| grateful if someone could guide me on it.
|
| My interpretation of it so far:
|
| Vlookup the value in the table, when choosing column to bring back do
| not look past column 10, do not look before column 2, instead look for
| column 2 + rounded up figure (0 decimal places) for the max. of .....
| here's where I hit a brick wall. And I can't seem to figure out the -23
| and the /12 parts.
|
| Also I'm wondering if I could use the Roundup formula as opposed to the
| Ceiling one?
|
| Big thanks in advance for any assistance or feedback you can give.
|
|
| --
| campka
| ------------------------------------------------------------------------
| campka's Profile: http://www.excelforum.com/member.php...o&userid=37533
| View this thread: http://www.excelforum.com/showthread...hreadid=571749
|

Hi

Something like this (check limit values in array parameter for MATCH - maybe
you have to increase them by 1)

=IF(OR($K65>=118,ISERROR(VLOOKUP($M65,Detail!$O$5:$X$26,MATCH($K65,{0;23;35;47;59;71;82;94;106},0)+1,0))),0,VLOOKUP($M65,Detail!$O$5:$X$26,MATCH($K65,{0;23;35;47;59;71;82;94;106},0)+1,0))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"campka" <[email protected]> wrote in
message news:[email protected]...
>
> Guys, I am brand new here and with a mere average knowledge of excel and
> I originally had a huge formula with multiple nested statements, but
> because Excel cannot nest more than 7, I needed to come up with a
> workaround. The formula below is ideally what I required:
>
>
> Code:
> --------------------
>
> =(If(ISERROR((If($K65<=23,VLOOKUP($M65,Detail!$O$5:$X$26,2,0),If($K65<=35,VLOOKUP($M65,Detail!$O$5:$X$26,3,0),If($K65<=47,VLOOKUP($M65,Detail!$O$5:$X$26,4,0),If($K65<=59,VLOOKUP($M65,Detail!$O$5:$X$26,5,0),If($K65<=71,VLOOKUP($M65,Detail!$O$5:$X$26,6,0),If($K65<=82,VLOOKUP($M65,Detail!$O$5:$X$26,7,0),If($K65<=94,VLOOKUP($M65,Detail!$O$5:$X$26,8,0),If($K65<=106,VLOOKUP($M65,Detail!$O$5:$X$26,9,0),If($K65<=118,VLOOKUP($M65,Detail!$O$5:$X$26,10,0)))))))))))))),0,(If($K65<=23,VLOOKUP($M65,Detail!$O$5:$X$26,2,0),If($K65<=35,VLOOKUP($M65,Detail!$O$5:$X$26,3,0),If($K65<=47,VLOOKUP($M65,Detail!$O$5:$X$26,4,0),If($K65<=59,VLOOKUP($M65,Detail!$O$5:$X$26,5,0),If($K65<=71,VLOOKUP($M65,Detail!$O$5:$X$26,6,0),If($K65<=82,VLOOKUP($M65,Detail!$O$5:$X$26,7,0,If($K65<=94,VLOOKUP($M65,Detail!$O$5:$X$26,8,0),If($K65<=106,VLOOKUP($M65,Detail!$O$5:$X$26,9,0),If($K65<=118,VLOOKUP($M65,Detail!$O$5:$X$26,10,0))))))))))))
> --------------------
>
>
>
> Now I was able to get some fantastic help on this to simplify to the
> following formula which is in fact working perfectly:
>
>
> Code:
> --------------------
>
> =IF(ISERROR(VLOOKUP($M65,Detail!$O$5:$X$26,MIN(10,(MAX(2,2+CEILING(MAX(0,(K65+(K65>82)-23))/12,1)))),0)),0,VLOOKUP($M65,Detail!$O$5:$X$26,MIN(10,(MAX(2,2+CEILING(MAX(0,(K65+(K65>82)-23))/12,1)))),0))
> --------------------
>
>
> The problem is that I am having difficulty understanding in layman's
> terms what the formula actually means and although its working
> perfectly, I am not confident with it and I really need to know the
> workings behind it. Unfortunately the person who assisted me with this
> so far is not available at the moment, so I'm throwing it out to you
> guys to see if anyone can shed any light on it. I would be so so
> grateful if someone could guide me on it.
>
> My interpretation of it so far:
>
> Vlookup the value in the table, when choosing column to bring back do
> not look past column 10, do not look before column 2, instead look for
> column 2 + rounded up figure (0 decimal places) for the max. of .....
> here's where I hit a brick wall. And I can't seem to figure out the -23
> and the /12 parts.
>
> Also I'm wondering if I could use the Roundup formula as opposed to the
> Ceiling one?
>
> Big thanks in advance for any assistance or feedback you can give.
>
>
> --
> campka
> ------------------------------------------------------------------------
> campka's Profile:
> http://www.excelforum.com/member.php...o&userid=37533
> View this thread: http://www.excelforum.com/showthread...hreadid=571749
>