|
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: 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 |
Workaround for nesting more than 7 statements
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 | |
Workaround for nesting more than 7 statements
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<attarkon.ee ) "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 |
| All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com