Nested IF statements

[anmillin]

Hoping for some help with nested IF statements!

In the example below, I'm trying to calculate pricing for a range of products across different stocking units (pack sizes). There are multiple products against which I'm trying to apply a standard differential of:

AAA= CCC-$12
BBB= CCC-$5
CCC= reference price
DDD= CCC+$8

[/URL][/IMG]


So, if Column A=Column B and Pack Code = CCC, no change in price. If Column A=Column B and Pack code=DDD, the value should be Pack code CCC for that product + $8 etc. for all different products and pack codes.

Can this be done and if so, can you guide me on how?

 

[etaf]

CCC is the only price - it maybe worth putting that into a cell as reference - so you can change that - Lets assume its in cell Z1 as 150

=IF( AND( A2=B2, C2 = "CCC" ) , $Z$1, IF( AND(A2=B2, C2 = "AAA" ), $Z$1 -12 , IF( AND(A2=B2, C2 = "BBB" ) , $Z$1 -5, IF( AND(A2=B2, C2 = "DDD" ) , $Z$1 + 8 , "criteria NOT met" ))))

if you want to hardcode the value into the nested IF then change $Z$1 in the formula to 150

 

[anmillin]

CCC is the only price - it maybe worth putting that into a cell as reference - so you can change that - Lets assume its in cell Z1 as 150

=IF( AND( A2=B2, C2 = "CCC" ) , $Z$1, IF( AND(A2=B2, C2 = "AAA" ), $Z$1 -12 , IF( AND(A2=B2, C2 = "BBB" ) , $Z$1 -5, IF( AND(A2=B2, C2 = "DDD" ) , $Z$1 + 8 , "criteria NOT met" ))))

if you want to hardcode the value into the nested IF then change $Z$1 in the formula to 150

Thanks for the reply etaf. With regards to the reference cell Z1, will the formula still work with various values for CCC pack codes?

In the example, every product has a different price for CCC-can I calculate the other prices with a variable CCC value for each product?

 

[etaf]

sorry , i dont understand the question

you showed 4 packs and the formula calculates the 4 values based on the info provided
AAA= CCC-$12
BBB= CCC-$5
CCC= reference price
DDD= CCC+$8

and the Nested IF will work for those 4 packs and values as defined
However, if they are changing - then we can use a reference table to lookup all the differences for each pack

so in sheet2
if you had

AAA / -12
BBB / - 5
CCC / 0
DDD / +8

you can add more packs
although if you so this then you could include the actual value

in say sheet2 A1 = put the reference
then in the table you could add an extra column with the absolute value

A1 = 150
AAA / -12 / B2-$A$1
BBB / - 5 / B3-$A$1
CCC / 0 / B4-$A$1
DDD / +8 / B5-$A$1

Now all you need is a lookup

=index ( Sheet2!C:C, match( C2 , sheet2!A:A, 0 ))

with the IF to get if A2=B2

=IF( A2=B2, index ( Sheet2!C:C, match( C2 , sheet2!A:A, 0 )) , "Criteria NOT met" )

 

[jtakw]

Hi,

Perhaps you can set your table up like this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Product Code	Product Code	Pack Code	Description	Price		Product	Product Price	Product Code	Price Adjustment
2	Product1AAA	Product1AAA	AAA	Product 1	138		Product 1	150	AAA	($12)
3	Product1BBB	Product1BBB	BBB	Product 1	145		Product 2	155	BBB	($5)
4	Product1CCC	Product1CCC	CCC	Product 1	150		Product 3	160	CCC	0
5	Product1DDD	Product1DDD	DDD	Product 1	158				DDD	$8
6	Product2AAA	Product2AAA	AAA	Product 2	143
7	Product2BBB	Product2BBB	BBB	Product 2	150
8	Product2CCC	Product2CCC	CCC	Product 2	155
9	Product2DDD	Product2DDD	DDD	Product 2	163
10	Product3AAA	Product3AAA	AAA	Product 3	148
11	Product3BBB	Product3BBB	BBB	Product 3	155
12	Product3CCC	Product3CCC	CCC	Product 3	160
13	Product3DDD	Product3DDD	DDD	Product 3	168
Sheet1

Cell Formulas
Range	Formula
E2	=IF(A2=B2,VLOOKUP(D2,G$2:H$4,2)+VLOOKUP(C2,I$2:J$5,2),"")

E2 formula copied down.

 

[anmillin]

sorry , i dont understand the question

you showed 4 packs and the formula calculates the 4 values based on the info provided
AAA= CCC-$12
BBB= CCC-$5
CCC= reference price
DDD= CCC+$8

and the Nested IF will work for those 4 packs and values as defined
However, if they are changing - then we can use a reference table to lookup all the differences for each pack

so in sheet2
if you had

AAA / -12
BBB / - 5
CCC / 0
DDD / +8

you can add more packs
although if you so this then you could include the actual value

in say sheet2 A1 = put the reference
then in the table you could add an extra column with the absolute value

A1 = 150
AAA / -12 / B2-$A$1
BBB / - 5 / B3-$A$1
CCC / 0 / B4-$A$1
DDD / +8 / B5-$A$1

Now all you need is a lookup

=index ( Sheet2!C:C, match( C2 , sheet2!A:A, 0 ))

with the IF to get if A2=B2

=IF( A2=B2, index ( Sheet2!C:C, match( C2 , sheet2!A:A, 0 )) , "Criteria NOT met" )

Sorry etaf, I didn't explain very well. As I understand, you're calculation will work if the price is constant for pack size CCC for every product. My issue is that CCC price is different for every product. It wouldn't be possible to have a reference price as it's variable.

 

[etaf]

confused
you said CCC was a reference of 150
and so thats what is used to get the values for AAA, BBB, DDD

but if the CCC price changes in the future from 150 - then having to change formulas where its hardcoded - buy having the 150 in one cell, all you need to do is to change that one cell to the new reference price and all the other values will now changed based on the new reference value of CCC

 

[jtakw]

Have you checked out my post #5??? Goodnight.