Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shorten a Formula
I have a formula that is pretty long. It works, but I have to propagate it
into 500+ other cells and the editing will take a long time. Anyway, please take a look and see if there is any way I can shorten this up. I'm sure there is, but I'm at my limits. =IF(ISNA(IF(ISNA(VLOOKUP(A6,A99:A168,1,FALSE)=A6), (0+VLOOKUP(A6,G99:H168,2,FALSE)),IF(ISNA(VLOOKUP(A 6,G99:G168,1,FALSE)=A6),(0+VLOOKUP(A6,A99:B168,2,F ALSE)),VLOOKUP(A6,A99:B168,2,FALSE)+VLOOKUP(A6,G99 :H168,2,FALSE)))),0,IF(ISNA(VLOOKUP(A6,A99:A168,1, FALSE)=A6),(0+VLOOKUP(A6,G99:H168,2,FALSE)),IF(ISN A(VLOOKUP(A6,G99:G168,1,FALSE)=A6),(0+VLOOKUP(A6,A 99:B168,2,FALSE)),VLOOKUP(A6,A99:B168,2,FALSE)+VLO OKUP(A6,G99:H168,2,FALSE)))) Thanks, Kevin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shorten a Formula
Could you tell us what this is supposed to do? It's riddled with
redundancy!! As for the difficulty of copying it, I wonder if the challenge is just because the tables used in the vlookup use relative references. That is, if you make the vlookup something like vlookup(a6,$a$99:$a$168,1,false) instead of vlookup(a6,a99,b168,1,false), then when you copy the formula, the table reference will remain constant ('absolute'). --Bruce "Kevin" wrote: I have a formula that is pretty long. It works, but I have to propagate it into 500+ other cells and the editing will take a long time. Anyway, please take a look and see if there is any way I can shorten this up. I'm sure there is, but I'm at my limits. =IF(ISNA(IF(ISNA(VLOOKUP(A6,A99:A168,1,FALSE)=A6), (0+VLOOKUP(A6,G99:H168,2,FALSE)),IF(ISNA(VLOOKUP(A 6,G99:G168,1,FALSE)=A6),(0+VLOOKUP(A6,A99:B168,2,F ALSE)),VLOOKUP(A6,A99:B168,2,FALSE)+VLOOKUP(A6,G99 :H168,2,FALSE)))),0,IF(ISNA(VLOOKUP(A6,A99:A168,1, FALSE)=A6),(0+VLOOKUP(A6,G99:H168,2,FALSE)),IF(ISN A(VLOOKUP(A6,G99:G168,1,FALSE)=A6),(0+VLOOKUP(A6,A 99:B168,2,FALSE)),VLOOKUP(A6,A99:B168,2,FALSE)+VLO OKUP(A6,G99:H168,2,FALSE)))) Thanks, Kevin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shorten a Formula
I know it's a horrible thing to look at, but I don't have much (if any)
experience with formulas, so it's what I got to work. It's supposed to take an exact match from A6, compare it to the Vlookup. If it's there, look in the other Vlookup for A6. If A6 is in the second Vlookup, add the two values in the 2nd column of the Vlookup's together and drop in the cell. If A6 results in N/A, it should assign a zero for the N/A in either lookup when adding them together. Sometimes A6 will exist in both lookups, sometimes only in one, and sometimes never. I have to at least have a result of 0 in the final result, and never an N/A, for my import into another database from the spreadsheet to work. Thank you for the "absolute" information. That saved me a ton of time copying the formula. I would like to make it a bit more logical, so if you have any advice it would be appreciated. Again, thanks for the help. Kevin "bpeltzer" wrote: Could you tell us what this is supposed to do? It's riddled with redundancy!! As for the difficulty of copying it, I wonder if the challenge is just because the tables used in the vlookup use relative references. That is, if you make the vlookup something like vlookup(a6,$a$99:$a$168,1,false) instead of vlookup(a6,a99,b168,1,false), then when you copy the formula, the table reference will remain constant ('absolute'). --Bruce "Kevin" wrote: I have a formula that is pretty long. It works, but I have to propagate it into 500+ other cells and the editing will take a long time. Anyway, please take a look and see if there is any way I can shorten this up. I'm sure there is, but I'm at my limits. =IF(ISNA(IF(ISNA(VLOOKUP(A6,A99:A168,1,FALSE)=A6), (0+VLOOKUP(A6,G99:H168,2,FALSE)),IF(ISNA(VLOOKUP(A 6,G99:G168,1,FALSE)=A6),(0+VLOOKUP(A6,A99:B168,2,F ALSE)),VLOOKUP(A6,A99:B168,2,FALSE)+VLOOKUP(A6,G99 :H168,2,FALSE)))),0,IF(ISNA(VLOOKUP(A6,A99:A168,1, FALSE)=A6),(0+VLOOKUP(A6,G99:H168,2,FALSE)),IF(ISN A(VLOOKUP(A6,G99:G168,1,FALSE)=A6),(0+VLOOKUP(A6,A 99:B168,2,FALSE)),VLOOKUP(A6,A99:B168,2,FALSE)+VLO OKUP(A6,G99:H168,2,FALSE)))) Thanks, Kevin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shorten a Formula
You should only need this =IF(ISNA(MATCH(A6,A99:A168,0)),0,VLOOKUP(A6,A99:B1 68,2,0))+IF(ISNA(MATCH(A6,G99:G168,0)),0,VLOOKUP(A 6,G99:H168,2,0)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=505501 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shorten a Formula
Thanks, it works great.
Kevin "daddylonglegs" wrote: You should only need this =IF(ISNA(MATCH(A6,A99:A168,0)),0,VLOOKUP(A6,A99:B1 68,2,0))+IF(ISNA(MATCH(A6,G99:G168,0)),0,VLOOKUP(A 6,G99:H168,2,0)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=505501 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Shorten an IF formula | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Shorten sumproduct formula | Excel Discussion (Misc queries) |