Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested 'If" stmts
I tried to be as detailed as possible:
I have version 2002 SP3 Can someone provide me a suggestion on formulas for the following: Scenario - Steve and Joe have red, green, yellow and blue widgets. Each widget weighs either 1,2,3 or 4 ounces. The price of the widget is based on all three variables i.e a 1 ounce red widget owned by Steve has a different cost then a 1 ounce red widget owned by Joe. So, what I need are formulas that will populate cells with the correct prices based on these variables. Ex: Steve owns six - 1 ounce red widgets. Column A contains the name of the owner (Steve / Joe) Column B contains the color of the widget. Column C contains the weight of the widget (1,2,3,4 ozs). Column D contains the number of widgets. Based on how the formulas could work, I don't mind having separate columns for the results for the different colors of widgets. For example, if column E was the column to capture the cost for the red widgets, the formula should give the answer to: If cloumn A = Steve and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $2 (cost of a 1 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $4 (cost of a 2 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $6 (cost of a 3 ounce red widget owned by Steve) however, if cloumn A = Steve and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $8 (cost of a 4 ounce red widget owned by Steve) however If cloumn A = Joe and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $3 (cost of a 1 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $5 (cost of a 2 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $7 (cost of a 3 ounce red widget owned by Joe) however, if cloumn A = Joe and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $9 (cost of a 4 ounce red widget owned by Joe) Columns F, G and H could have the same formula but with a different color for each column. I can sum the columns to get the value by color. Tx, Samoan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested 'If" stmts
On Tue, 28 Oct 2008 12:44:05 -0700, samoan
wrote: I tried to be as detailed as possible: I have version 2002 SP3 Can someone provide me a suggestion on formulas for the following: Scenario - Steve and Joe have red, green, yellow and blue widgets. Each widget weighs either 1,2,3 or 4 ounces. The price of the widget is based on all three variables i.e a 1 ounce red widget owned by Steve has a different cost then a 1 ounce red widget owned by Joe. So, what I need are formulas that will populate cells with the correct prices based on these variables. Ex: Steve owns six - 1 ounce red widgets. Column A contains the name of the owner (Steve / Joe) Column B contains the color of the widget. Column C contains the weight of the widget (1,2,3,4 ozs). Column D contains the number of widgets. Based on how the formulas could work, I don't mind having separate columns for the results for the different colors of widgets. For example, if column E was the column to capture the cost for the red widgets, the formula should give the answer to: If cloumn A = Steve and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $2 (cost of a 1 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $4 (cost of a 2 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $6 (cost of a 3 ounce red widget owned by Steve) however, if cloumn A = Steve and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $8 (cost of a 4 ounce red widget owned by Steve) however If cloumn A = Joe and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $3 (cost of a 1 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $5 (cost of a 2 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $7 (cost of a 3 ounce red widget owned by Joe) however, if cloumn A = Joe and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $9 (cost of a 4 ounce red widget owned by Joe) Columns F, G and H could have the same formula but with a different color for each column. I can sum the columns to get the value by color. Tx, Samoan I don't understand your columns F, G or H But for Column E: 1. Set up two tables: One for Joe, the other for Steve. NAME Steve's table "Steve" and Joe's table "Joe". They should look like this (you've only give info for red widgets, so that's all I was able to fill in): 1 2 3 4 red $2.00 $4.00 $6.00 $8.00 green yellow blue Where the values for Steve's different weighted; different colored widgets are properly filled in. Joe's table would look like: 1 2 3 4 red $3.00 $5.00 $7.00 $9.00 green yellow blue ---------------------------------- Given your values in columns A, B, C, and D, the formula in Column E would be: =VLOOKUP(B2,INDIRECT(A2),C2+1,FALSE)*D2 The "name" in column A is used by the INDIRECT function to tell VLOOKUP which table to search. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested 'If" stmts
Samoan - hopefully you will quickly find out that there are many different
ways to solve this one solution Build a table like the following (in my example this information is in the range b2:e9 Steve Red 1 2 Steve Red 2 4 Steve Red 3 6 Steve Red 4 8 Joe Red 1 3 Joe Red 2 5 Joe Red 3 7 Joe Red 4 9 g2:I2 have the inputs Steve Red 3 then in J2 have the following formula =SUMPRODUCT(--(B2:B9=G2),--(C2:C9=H2),--(D2:D9=I2),(E2:E9)) This will provide the answer of 6 (which is the multiplier that you were looking for - if I read your message correctly). Does this help. -- Wag more, bark less "samoan" wrote: I tried to be as detailed as possible: I have version 2002 SP3 Can someone provide me a suggestion on formulas for the following: Scenario - Steve and Joe have red, green, yellow and blue widgets. Each widget weighs either 1,2,3 or 4 ounces. The price of the widget is based on all three variables i.e a 1 ounce red widget owned by Steve has a different cost then a 1 ounce red widget owned by Joe. So, what I need are formulas that will populate cells with the correct prices based on these variables. Ex: Steve owns six - 1 ounce red widgets. Column A contains the name of the owner (Steve / Joe) Column B contains the color of the widget. Column C contains the weight of the widget (1,2,3,4 ozs). Column D contains the number of widgets. Based on how the formulas could work, I don't mind having separate columns for the results for the different colors of widgets. For example, if column E was the column to capture the cost for the red widgets, the formula should give the answer to: If cloumn A = Steve and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $2 (cost of a 1 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $4 (cost of a 2 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $6 (cost of a 3 ounce red widget owned by Steve) however, if cloumn A = Steve and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $8 (cost of a 4 ounce red widget owned by Steve) however If cloumn A = Joe and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $3 (cost of a 1 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $5 (cost of a 2 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $7 (cost of a 3 ounce red widget owned by Joe) however, if cloumn A = Joe and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $9 (cost of a 4 ounce red widget owned by Joe) Columns F, G and H could have the same formula but with a different color for each column. I can sum the columns to get the value by color. Tx, Samoan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested 'If" stmts
Ron,
Your response is helpful except I've never set up a table before, so don't know how to name it and make the vlookup formula find the table. I also want to keep the totals for the cost in separate colmns by color. Ex: Here's the raw data A B C D 1 Steve Red 2 200 2 Joe Blue 4 14 3 Steve Yellow 3 500 The calculations (Cost) needs to reside in the next set of columns (for the ex above) ( Note the column headings) Note the price for a Blue 4 oz widget for Joe is $40 each and the price for a Yellow 3 oz widget for Steve is $16 each ) Red widgets Green widgets Yellow Widgets Blue Widgets E F G H 1 $800.00 False (or blank) False (or blank) False (or blank) 2 False (or blank) False (or blank) False (or blank) $64.00 3 False (or blank) False (or blank) $8,000.00 False (or blank) Does the VLOOKUP formula get copied into each of the cells I want the results in, as in cells e1.h3? Where do I put the tables (Steve, Joe) in the spreadsheet and how do I reference them (you may have to resist telling me to take an excel class)? -- Tx, Samoan "Ron Rosenfeld" wrote: On Tue, 28 Oct 2008 12:44:05 -0700, samoan wrote: I tried to be as detailed as possible: I have version 2002 SP3 Can someone provide me a suggestion on formulas for the following: Scenario - Steve and Joe have red, green, yellow and blue widgets. Each widget weighs either 1,2,3 or 4 ounces. The price of the widget is based on all three variables i.e a 1 ounce red widget owned by Steve has a different cost then a 1 ounce red widget owned by Joe. So, what I need are formulas that will populate cells with the correct prices based on these variables. Ex: Steve owns six - 1 ounce red widgets. Column A contains the name of the owner (Steve / Joe) Column B contains the color of the widget. Column C contains the weight of the widget (1,2,3,4 ozs). Column D contains the number of widgets. Based on how the formulas could work, I don't mind having separate columns for the results for the different colors of widgets. For example, if column E was the column to capture the cost for the red widgets, the formula should give the answer to: If cloumn A = Steve and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $2 (cost of a 1 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $4 (cost of a 2 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $6 (cost of a 3 ounce red widget owned by Steve) however, if cloumn A = Steve and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $8 (cost of a 4 ounce red widget owned by Steve) however If cloumn A = Joe and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $3 (cost of a 1 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $5 (cost of a 2 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $7 (cost of a 3 ounce red widget owned by Joe) however, if cloumn A = Joe and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $9 (cost of a 4 ounce red widget owned by Joe) Columns F, G and H could have the same formula but with a different color for each column. I can sum the columns to get the value by color. Tx, Samoan I don't understand your columns F, G or H But for Column E: 1. Set up two tables: One for Joe, the other for Steve. NAME Steve's table "Steve" and Joe's table "Joe". They should look like this (you've only give info for red widgets, so that's all I was able to fill in): 1 2 3 4 red $2.00 $4.00 $6.00 $8.00 green yellow blue Where the values for Steve's different weighted; different colored widgets are properly filled in. Joe's table would look like: 1 2 3 4 red $3.00 $5.00 $7.00 $9.00 green yellow blue ---------------------------------- Given your values in columns A, B, C, and D, the formula in Column E would be: =VLOOKUP(B2,INDIRECT(A2),C2+1,FALSE)*D2 The "name" in column A is used by the INDIRECT function to tell VLOOKUP which table to search. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested 'If" stmts
On Wed, 29 Oct 2008 13:46:06 -0700, samoan
wrote: Ron, Your response is helpful except I've never set up a table before, so don't know how to name it and make the vlookup formula find the table. I also want to keep the totals for the cost in separate colmns by color. Ex: Here's the raw data A B C D 1 Steve Red 2 200 2 Joe Blue 4 14 3 Steve Yellow 3 500 The calculations (Cost) needs to reside in the next set of columns (for the ex above) ( Note the column headings) Note the price for a Blue 4 oz widget for Joe is $40 each and the price for a Yellow 3 oz widget for Steve is $16 each ) Red widgets Green widgets Yellow Widgets Blue Widgets E F G H 1 $800.00 False (or blank) False (or blank) False (or blank) 2 False (or blank) False (or blank) False (or blank) $64.00 3 False (or blank) False (or blank) $8,000.00 False (or blank) Does the VLOOKUP formula get copied into each of the cells I want the results in, as in cells e1.h3? Where do I put the tables (Steve, Joe) in the spreadsheet and how do I reference them (you may have to resist telling me to take an excel class)? 1. You can set up the tables anyplace on the worksheet, or even on another worksheet. After you have entered the data, select the range and NAME it. Use Excel HELP for NAME a Range. For example, let us say you choose to start Steve's table in R5. You would have made the following entries: R5: <blank S5: 1 T5: 2 U5: 3 V5: 4 R6: red S6: 2 T6: 4 U6: 6 V6: 8 R7: green S7: <blank T7: <blank U7: <blank V7: <blank R8: yellow S8: <blank T8: <blank U8: <blank V8: <blank R9: blue S9: <blank T9: <blank U9: <blank V9: <blank Note that R5 is blank and stays that way. The other blanks will get filled in with the appropriate value for weight and color of widget for Steve. You would then select the range R5:V9 and Define that Name as Steve. Repeat for Joe. ================================ As I wrote before, VLOOKUP "finds" the correct table using the INDIRECT(A2) function. In that cell you will have written either Steve or Joe, so it will go to the correct table. As you copy the formula down in each column, the A2 will adjust to reflect the proper row. However, one part that wasn't clear to me before is that col E should have only the total for the red widgets, and otherwise be blank; col F green and so forth. So let us set up your column headers as follows: A1: Owner B1: Color C1: Weight D1: Quantity E1: Red F1: Green G1: Yellow H1: Blue and then make a slight change in the formula. This formula should be entered in E2. Then fill right to H2 and down as far as required: =IF($B2=E$1,VLOOKUP($B2,INDIRECT($A2),$C2+1,FALSE) *$D2,"") The IF statement checks the color listed in column B with the color at the top of the "totals" column (E,F,G or H) and only does the calculation if they match. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested 'If" stmts
IT WORKS!!!!!!!! Tx Ron
-- Tx, Samoan "samoan" wrote: Ron, Your response is helpful except I've never set up a table before, so don't know how to name it and make the vlookup formula find the table. I also want to keep the totals for the cost in separate colmns by color. Ex: Here's the raw data A B C D 1 Steve Red 2 200 2 Joe Blue 4 14 3 Steve Yellow 3 500 The calculations (Cost) needs to reside in the next set of columns (for the ex above) ( Note the column headings) Note the price for a Blue 4 oz widget for Joe is $40 each and the price for a Yellow 3 oz widget for Steve is $16 each ) Red widgets Green widgets Yellow Widgets Blue Widgets E F G H 1 $800.00 False (or blank) False (or blank) False (or blank) 2 False (or blank) False (or blank) False (or blank) $64.00 3 False (or blank) False (or blank) $8,000.00 False (or blank) Does the VLOOKUP formula get copied into each of the cells I want the results in, as in cells e1.h3? Where do I put the tables (Steve, Joe) in the spreadsheet and how do I reference them (you may have to resist telling me to take an excel class)? -- Tx, Samoan "Ron Rosenfeld" wrote: On Tue, 28 Oct 2008 12:44:05 -0700, samoan wrote: I tried to be as detailed as possible: I have version 2002 SP3 Can someone provide me a suggestion on formulas for the following: Scenario - Steve and Joe have red, green, yellow and blue widgets. Each widget weighs either 1,2,3 or 4 ounces. The price of the widget is based on all three variables i.e a 1 ounce red widget owned by Steve has a different cost then a 1 ounce red widget owned by Joe. So, what I need are formulas that will populate cells with the correct prices based on these variables. Ex: Steve owns six - 1 ounce red widgets. Column A contains the name of the owner (Steve / Joe) Column B contains the color of the widget. Column C contains the weight of the widget (1,2,3,4 ozs). Column D contains the number of widgets. Based on how the formulas could work, I don't mind having separate columns for the results for the different colors of widgets. For example, if column E was the column to capture the cost for the red widgets, the formula should give the answer to: If cloumn A = Steve and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $2 (cost of a 1 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $4 (cost of a 2 ounce red widget owned by Steve), however, if cloumn A = Steve and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $6 (cost of a 3 ounce red widget owned by Steve) however, if cloumn A = Steve and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $8 (cost of a 4 ounce red widget owned by Steve) however If cloumn A = Joe and if Column B = red, and if column C = 1, then the result in Column E should be the amount in column D times $3 (cost of a 1 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 2, then the result in Column E should be the amount in column D times $5 (cost of a 2 ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B = red, and if column C = 3, then the result in Column E should be the amount in column D times $7 (cost of a 3 ounce red widget owned by Joe) however, if cloumn A = Joe and if Column B = red, and if column C = 4, then the result in Column E should be the amount in column D times $9 (cost of a 4 ounce red widget owned by Joe) Columns F, G and H could have the same formula but with a different color for each column. I can sum the columns to get the value by color. Tx, Samoan I don't understand your columns F, G or H But for Column E: 1. Set up two tables: One for Joe, the other for Steve. NAME Steve's table "Steve" and Joe's table "Joe". They should look like this (you've only give info for red widgets, so that's all I was able to fill in): 1 2 3 4 red $2.00 $4.00 $6.00 $8.00 green yellow blue Where the values for Steve's different weighted; different colored widgets are properly filled in. Joe's table would look like: 1 2 3 4 red $3.00 $5.00 $7.00 $9.00 green yellow blue ---------------------------------- Given your values in columns A, B, C, and D, the formula in Column E would be: =VLOOKUP(B2,INDIRECT(A2),C2+1,FALSE)*D2 The "name" in column A is used by the INDIRECT function to tell VLOOKUP which table to search. --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested 'If" stmts
On Fri, 31 Oct 2008 06:05:01 -0700, samoan
wrote: IT WORKS!!!!!!!! Tx Ron -- Tx, Samoan You're welcome. Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating macros with if stmts | Excel Discussion (Misc queries) | |||
Monthly bank stmts | Excel Worksheet Functions | |||
Need more than 7 nested "IF" statements in Excel" | Excel Discussion (Misc queries) | |||
"How do I correct error in Excel2003 for nested subtotal?" | Excel Discussion (Misc queries) | |||
IF Statement that's conditional on multiple IF Stmts in other cell | Excel Worksheet Functions |