Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
Here is a formula I'm using
=IF(B5=B4,IFERROR(HLOOKUP(VLOOKUP(B5,Referencias!$ BG$1:$BH$19,2,FALSE),Catalogo!$K$1:$Y$7,(IF(VLOOKU P(L5,Catalogo!$C$2:$F$1117,4,FALSE)<(VLOOKUP(L4,C atalogo!$C$2:$F$1117,4,FALSE)),5,(IF(L5<L4,IF(VLO OKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4 ,Catalogo!$C$2:$F$1117,2,FALSE)),6,IF(VLOOKUP(L5,C atalogo!$C$2:$F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo !$C$2:$F$1117,2,FALSE)),7,2)),IF(VLOOKUP(L5,Catalo go!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4,Catalogo!$C$2 :$F$1117,2,FALSE)),4,IF(VLOOKUP(L5,Catalogo!$C$2:$ F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo!$C$2:$F$1117, 2,FALSE)),3,0)))))),FALSE),0),0) I know, I know it's toooooo long what I'm doing is comparing a machine with the last one used, then seen if there is a swicth on the material that it needs. If true, this formula will find how long it´s going to take finding the values in a table. seems complicated because it search trought several tables could anyone think on an easier way?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
First question: Does it consistently provide you with valid results?
If the answer to that is YES, then leave it alone. I see some possibilities of reducing it to some degree MAYBE by using SUMPRODUCT() to return values out of the Catalogo!$C$2:$F$1117 table. But I'm not wading into this any deeper if your answer to my question was YES. If the answer was YES, are there other issues such as taking too much time to return a result? "Alonso" wrote: Here is a formula I'm using =IF(B5=B4,IFERROR(HLOOKUP(VLOOKUP(B5,Referencias!$ BG$1:$BH$19,2,FALSE),Catalogo!$K$1:$Y$7,(IF(VLOOKU P(L5,Catalogo!$C$2:$F$1117,4,FALSE)<(VLOOKUP(L4,C atalogo!$C$2:$F$1117,4,FALSE)),5,(IF(L5<L4,IF(VLO OKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4 ,Catalogo!$C$2:$F$1117,2,FALSE)),6,IF(VLOOKUP(L5,C atalogo!$C$2:$F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo !$C$2:$F$1117,2,FALSE)),7,2)),IF(VLOOKUP(L5,Catalo go!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4,Catalogo!$C$2 :$F$1117,2,FALSE)),4,IF(VLOOKUP(L5,Catalogo!$C$2:$ F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo!$C$2:$F$1117, 2,FALSE)),3,0)))))),FALSE),0),0) I know, I know it's toooooo long what I'm doing is comparing a machine with the last one used, then seen if there is a swicth on the material that it needs. If true, this formula will find how long it´s going to take finding the values in a table. seems complicated because it search trought several tables could anyone think on an easier way?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
That is a CrAzY function. Try sumproduct:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html Also, try using a Pivot Table: http://www.contextures.com/xlPivot02.html http://peltiertech.com/Excel/Pivots/pivottables.htm Regards, Ryan--- -- RyGuy "JLatham" wrote: First question: Does it consistently provide you with valid results? If the answer to that is YES, then leave it alone. I see some possibilities of reducing it to some degree MAYBE by using SUMPRODUCT() to return values out of the Catalogo!$C$2:$F$1117 table. But I'm not wading into this any deeper if your answer to my question was YES. If the answer was YES, are there other issues such as taking too much time to return a result? "Alonso" wrote: Here is a formula I'm using =IF(B5=B4,IFERROR(HLOOKUP(VLOOKUP(B5,Referencias!$ BG$1:$BH$19,2,FALSE),Catalogo!$K$1:$Y$7,(IF(VLOOKU P(L5,Catalogo!$C$2:$F$1117,4,FALSE)<(VLOOKUP(L4,C atalogo!$C$2:$F$1117,4,FALSE)),5,(IF(L5<L4,IF(VLO OKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4 ,Catalogo!$C$2:$F$1117,2,FALSE)),6,IF(VLOOKUP(L5,C atalogo!$C$2:$F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo !$C$2:$F$1117,2,FALSE)),7,2)),IF(VLOOKUP(L5,Catalo go!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4,Catalogo!$C$2 :$F$1117,2,FALSE)),4,IF(VLOOKUP(L5,Catalogo!$C$2:$ F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo!$C$2:$F$1117, 2,FALSE)),3,0)))))),FALSE),0),0) I know, I know it's toooooo long what I'm doing is comparing a machine with the last one used, then seen if there is a swicth on the material that it needs. If true, this formula will find how long it´s going to take finding the values in a table. seems complicated because it search trought several tables could anyone think on an easier way?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
thanks for your reply J
the answer to your question is YES this formula works fine the main issue to try to simplify this it's because it has reached the nesting limit altroght not critical, i was trying to include another IF to check another data right now, I can't do that I'll try with the sumproduct and get back with news "JLatham" wrote: First question: Does it consistently provide you with valid results? If the answer to that is YES, then leave it alone. I see some possibilities of reducing it to some degree MAYBE by using SUMPRODUCT() to return values out of the Catalogo!$C$2:$F$1117 table. But I'm not wading into this any deeper if your answer to my question was YES. If the answer was YES, are there other issues such as taking too much time to return a result? "Alonso" wrote: Here is a formula I'm using =IF(B5=B4,IFERROR(HLOOKUP(VLOOKUP(B5,Referencias!$ BG$1:$BH$19,2,FALSE),Catalogo!$K$1:$Y$7,(IF(VLOOKU P(L5,Catalogo!$C$2:$F$1117,4,FALSE)<(VLOOKUP(L4,C atalogo!$C$2:$F$1117,4,FALSE)),5,(IF(L5<L4,IF(VLO OKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4 ,Catalogo!$C$2:$F$1117,2,FALSE)),6,IF(VLOOKUP(L5,C atalogo!$C$2:$F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo !$C$2:$F$1117,2,FALSE)),7,2)),IF(VLOOKUP(L5,Catalo go!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4,Catalogo!$C$2 :$F$1117,2,FALSE)),4,IF(VLOOKUP(L5,Catalogo!$C$2:$ F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo!$C$2:$F$1117, 2,FALSE)),3,0)))))),FALSE),0),0) I know, I know it's toooooo long what I'm doing is comparing a machine with the last one used, then seen if there is a swicth on the material that it needs. If true, this formula will find how long it´s going to take finding the values in a table. seems complicated because it search trought several tables could anyone think on an easier way?? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
You've got IFERROR in there, which implies that you are using Excel 2007,
which I thought had a nesting limit of 64 - your formula does not exceed that, does it? (I've not looked at it in detail as I only have Excel 2000) Pete "Alonso" wrote in message ... thanks for your reply J the answer to your question is YES this formula works fine the main issue to try to simplify this it's because it has reached the nesting limit altroght not critical, i was trying to include another IF to check another data right now, I can't do that I'll try with the sumproduct and get back with news "JLatham" wrote: First question: Does it consistently provide you with valid results? If the answer to that is YES, then leave it alone. I see some possibilities of reducing it to some degree MAYBE by using SUMPRODUCT() to return values out of the Catalogo!$C$2:$F$1117 table. But I'm not wading into this any deeper if your answer to my question was YES. If the answer was YES, are there other issues such as taking too much time to return a result? "Alonso" wrote: Here is a formula I'm using =IF(B5=B4,IFERROR(HLOOKUP(VLOOKUP(B5,Referencias!$ BG$1:$BH$19,2,FALSE),Catalogo!$K$1:$Y$7,(IF(VLOOKU P(L5,Catalogo!$C$2:$F$1117,4,FALSE)<(VLOOKUP(L4,C atalogo!$C$2:$F$1117,4,FALSE)),5,(IF(L5<L4,IF(VLO OKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4 ,Catalogo!$C$2:$F$1117,2,FALSE)),6,IF(VLOOKUP(L5,C atalogo!$C$2:$F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo !$C$2:$F$1117,2,FALSE)),7,2)),IF(VLOOKUP(L5,Catalo go!$C$2:$F$1117,2,FALSE)(VLOOKUP(L4,Catalogo!$C$2 :$F$1117,2,FALSE)),4,IF(VLOOKUP(L5,Catalogo!$C$2:$ F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo!$C$2:$F$1117, 2,FALSE)),3,0)))))),FALSE),0),0) I know, I know it's toooooo long what I'm doing is comparing a machine with the last one used, then seen if there is a swicth on the material that it needs. If true, this formula will find how long it´s going to take finding the values in a table. seems complicated because it search trought several tables could anyone think on an easier way?? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
Hi Pete
You're right I'm using Excel 2007 the reason i got the nesting error is because I save this archive in 97-2003 format not everyone in the office has 2007, and I need that they can see the file it's there a potencial problem with the IFERROR formula?? "Pete_UK" wrote: You've got IFERROR in there, which implies that you are using Excel 2007, which I thought had a nesting limit of 64 - your formula does not exceed that, does it? (I've not looked at it in detail as I only have Excel 2000) Pete |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
Excel 2003 and earlier versions will not recognise IFERROR, so you will
probably have a #NAME error if you try to load the file on those versions. With regard to the nesting problem, I suggest you split your formula into smaller chunks over a few columns and then combine them using these intermediate columns. Hope this helps. Pete "Alonso" wrote in message ... Hi Pete You're right I'm using Excel 2007 the reason i got the nesting error is because I save this archive in 97-2003 format not everyone in the office has 2007, and I need that they can see the file it's there a potencial problem with the IFERROR formula?? "Pete_UK" wrote: You've got IFERROR in there, which implies that you are using Excel 2007, which I thought had a nesting limit of 64 - your formula does not exceed that, does it? (I've not looked at it in detail as I only have Excel 2000) Pete |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
thanks Pete
probably that will work split in 2 in columns and get the max between those?? after all the formula gets only 1 value "Pete_UK" wrote: Excel 2003 and earlier versions will not recognise IFERROR, so you will probably have a #NAME error if you try to load the file on those versions. With regard to the nesting problem, I suggest you split your formula into smaller chunks over a few columns and then combine them using these intermediate columns. Hope this helps. Pete "Alonso" wrote in message ... Hi Pete You're right I'm using Excel 2007 the reason i got the nesting error is because I save this archive in 97-2003 format not everyone in the office has 2007, and I need that they can see the file it's there a potencial problem with the IFERROR formula?? "Pete_UK" wrote: You've got IFERROR in there, which implies that you are using Excel 2007, which I thought had a nesting limit of 64 - your formula does not exceed that, does it? (I've not looked at it in detail as I only have Excel 2000) Pete |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to avoid nested IFs
Glad to be of help.
Pete On Sep 24, 5:43*pm, Alonso wrote: thanks Pete probably that will work split in 2 in columns and get the max between those?? after all the formula gets only 1 value "Pete_UK" wrote: Excel 2003 and earlier versions will not recognise IFERROR, so you will probably have a #NAME error if you try to load the file on those versions. With regard to the nesting problem, I suggest you split your formula into smaller chunks over a few columns and then combine them using these intermediate columns. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
avoid nested IFs with another function | Excel Worksheet Functions | |||
IF isna to avoid #N/A | Excel Discussion (Misc queries) | |||
nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
How to avoid name ref in formula? | Excel Discussion (Misc queries) | |||
what is this, how do I avoid it? | Excel Discussion (Misc queries) |