http://www.cpearson.com/excel/nested.htm
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"campka" wrote in message
...
|
| 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),I f($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$2 6,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),I f($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+(K6582)-23))/12,1)))),0)),0,VLOOKUP($M65,Detail!$O$5:$X$26,MIN( 10,(MAX(2,2+CEILING(MAX(0,(K65+(K6582)-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
|