Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternative formula to the vlookup formula?
I have data arranged like this:
a b c d e f assembly Part No. price xyz1 xyz2 xyz3 TOTAL abc1 1 3 1 xxx abc2 2 2 1 xxx abc3 3 1 3 xxx xyz1 .4 xyz2 .5 xyz3 .6 Part abc1 is assembled with 3 pieces of xyz1 and 1 piece of xyz2. I would like to have the cells under "TOTAL" give the total price of that part including the assembly components so for part no. abc1, I want the price to show $2.70, part no. abc2, to show $3.6 and part no. abc3 to show 5.2. I am currently using a =sum(vlookup*c3, vlookup*d3, vlookup*e3) formula which works great except that there are so many assembly items that I cannot add any more functions to the cell. Is there a different forumla which would allow me to find the total price? If you need additional information, please let me know. Thanks, Victor |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternative formula to the vlookup formula?
Hi Victor,
It's pretty easy to solve if you can cheat a bit. If you put a VLOOKUP formula in a row directly above the assemply numbers and get the prices there then you don't have to use the lookup function in your total formula. Here's a way that would work IF you are OK with that idea. In C3 (where row 4 has your assembly numbers) enter VLOOKUP((C4,$A$5:$B$10,2,FALSE) Probably would be best to use a named range for the Table-array range rather than the cell references Copy that formula across all the columns in which you have assemblies--in your example that would be C3:E3, I think Then use this formula for your totals in column F: In cell F5 (where row 5 is the row just below the assembly numbers and contains your first part--abc1) enter =B5+SUMPRODUCT(C$3:E$3,C5:E5) and copy that formula down into all the rows for which you have parts. I'm sure there's a way to do this without the VLOOKUP row, but I can't think of it off the top of my head. Of course you can hide the VLOOKUP row so only you know it's there... "Victor" wrote: I have data arranged like this: a b c d e f assembly Part No. price xyz1 xyz2 xyz3 TOTAL abc1 1 3 1 xxx abc2 2 2 1 xxx abc3 3 1 3 xxx xyz1 .4 xyz2 .5 xyz3 .6 Part abc1 is assembled with 3 pieces of xyz1 and 1 piece of xyz2. I would like to have the cells under "TOTAL" give the total price of that part including the assembly components so for part no. abc1, I want the price to show $2.70, part no. abc2, to show $3.6 and part no. abc3 to show 5.2. I am currently using a =sum(vlookup*c3, vlookup*d3, vlookup*e3) formula which works great except that there are so many assembly items that I cannot add any more functions to the cell. Is there a different forumla which would allow me to find the total price? If you need additional information, please let me know. Thanks, Victor |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alternative formula to the vlookup formula?
Hi Ted.
Thanks for your help. There wasn't time to use your suggestion since we would like to push forward with the spreadsheet so we used another "cheat" to get our totals. I'll keep your information on hand for the next time we retool the spreadsheet though. What we did was make two subtotals and then added them to get a final total. Thanks again for your help. "Ted M H" wrote: Hi Victor, It's pretty easy to solve if you can cheat a bit. If you put a VLOOKUP formula in a row directly above the assemply numbers and get the prices there then you don't have to use the lookup function in your total formula. Here's a way that would work IF you are OK with that idea. In C3 (where row 4 has your assembly numbers) enter VLOOKUP((C4,$A$5:$B$10,2,FALSE) Probably would be best to use a named range for the Table-array range rather than the cell references Copy that formula across all the columns in which you have assemblies--in your example that would be C3:E3, I think Then use this formula for your totals in column F: In cell F5 (where row 5 is the row just below the assembly numbers and contains your first part--abc1) enter =B5+SUMPRODUCT(C$3:E$3,C5:E5) and copy that formula down into all the rows for which you have parts. I'm sure there's a way to do this without the VLOOKUP row, but I can't think of it off the top of my head. Of course you can hide the VLOOKUP row so only you know it's there... "Victor" wrote: I have data arranged like this: a b c d e f assembly Part No. price xyz1 xyz2 xyz3 TOTAL abc1 1 3 1 xxx abc2 2 2 1 xxx abc3 3 1 3 xxx xyz1 .4 xyz2 .5 xyz3 .6 Part abc1 is assembled with 3 pieces of xyz1 and 1 piece of xyz2. I would like to have the cells under "TOTAL" give the total price of that part including the assembly components so for part no. abc1, I want the price to show $2.70, part no. abc2, to show $3.6 and part no. abc3 to show 5.2. I am currently using a =sum(vlookup*c3, vlookup*d3, vlookup*e3) formula which works great except that there are so many assembly items that I cannot add any more functions to the cell. Is there a different forumla which would allow me to find the total price? If you need additional information, please let me know. Thanks, Victor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative formula for getting totals | Excel Worksheet Functions | |||
too many arguments in formula. alternative? | Excel Discussion (Misc queries) | |||
alternative to VLOOKUP | Excel Worksheet Functions | |||
Vlookup Alternative Needed | Excel Discussion (Misc queries) | |||
Nested Vlookup or alternative? | Excel Worksheet Functions |