Nested 'If" stmts

[samoan]

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

 

[Ron Rosenfeld]

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

 

[Brad]

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),--(D29=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.

 

[samoan]

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)?

 

[Ron Rosenfeld]

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

 

[samoan]

IT WORKS!!!!!!!! Tx Ron
--
Tx,
Samoan

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)?

 

[Ron Rosenfeld]

IT WORKS!!!!!!!! Tx Ron

You're welcome. Glad to help. Thanks for the feedback.
--ron