Conditional calculation

[scottlarock]

Hi there,

I need to improve a formula serving to calculate a value. here is the scenario :

Products A (in column F) should not be more expensive than Products B or C within the same country (column C).
If it just so happens to be the case, the price of Products A should be priced 5 % less than Products B or 8 % less than Products C.
In this example, Z24 should be 8% less than Z26.

Any clue what the perfect formula would look like ? :°)

	A	B	C	F	X	Y	Z
1	Region	Cust Type	Country	Product	Price 12	Quantity 12	Hypothetical D Price
24	Reg 1	Hosp	Cntry 1	A	920	88	1,058.2
25	Reg 1	Hosp	Cntry 1	B		2
26	Reg 1	Hosp	Cntry 1	C	1,106	0	1,005.5

<tbody>
</tbody>

Tons of thanks in advance,
Scott

 

[memar_one]

Perhaps:

=IF(Z25="", .92*Z26, .95*Z25)

Not exactly sure what you're asking for. What do you want to happen if the products are the same, or if the countries are different? And this formula also assumes that at least either Z25 or Z26 is filled, and doesn't take into account a change in country (because I don't know what you want to happen in that case).

 

[scottlarock]

Perhaps:

=IF(Z25="", .92*Z26, .95*Z25)

Not exactly sure what you're asking for. What do you want to happen if the products are the same, or if the countries are different? And this formula also assumes that at least either Z25 or Z26 is filled, and doesn't take into account a change in country (because I don't know what you want to happen in that case).

Thx for the reply. Sorry for being unclear. I tried to make it soind as simple as possible.
My current formula is the following :

IF(AND($F3='Lookup data'!$A$4,X3>HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE)),X3*1.1,
IF(AND($F3='Lookup data'!$A$5,X3>HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE)),X3/1.1,
IF(AND($F3='Lookup data'!$A$6,X3>HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE)),X3/1.1,
MIN(HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE),X3*(1+$AA$1)))))

Now, i just need to add a final condition which i explained in my initial post above.
The condition explained above only applies to similar countries ie usa price vs usa price, not mexico price vs usa price.
If u dont kno, no problem my friend.. The thing is it's a bit more tedious than a simple if formula.
Scott

 

[memar_one]

I'm assuming products wont repeat, and that the above formula was pasted into Z3. "Lookup data" must be the sheet that holds data for each product.

So this is your chain of statements:

If the product is product 1, and "Price 12" for this product is greater than its type's value, then display Price 12 times 1.1 in a cell in column z.
Elseif the product is product 2 and price 12 is greater than its type's value, then display price 12 divided by 1.1
Elseif the product is product 3 and the price is greater than its type's value, then display price 12 divided by 1.1
Else display either X3*(1+ whatever's in AA1) or its type's value, whichever's smaller.

In my own thinking, this is what the chain is "simplified":

Step 1. Find the matching product.

Assume product's "price 12" is 900 and its type's value is 1000.

Step 2.
If "Price 12" for the product, is > 1000, then display whatever the calculation is for that product in column Z
ElseIf price 12 for the product is < 1000, then display whatever's smaller; 1000 or some other calculation using price 12.

and it ends there; you want to add more.

Your conditionals now want to be:

Step 1. find the product

Step 2.
If "Price 12" for the product, is > 1000, then display whatever the calculation is for that product in column Z
Else if the product is <= 1000 but greater than the other products, then display whatever's smaller; 1000 or some other calculation using price 12.
Else if the product is <= 1000 AND < TWO other products, then display the most expensive's product's price times .92
Else if the product is <= 1000 AND < one other product, then display that other product's price times .95

End result of my thinking:

IF($F3='Lookup data'!$A$4,
IF(X3>HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE),X3*1.1,
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), X3>OTHER_PRODUCT1, X3>OTHER_PRODUCT2),
MIN(HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE),X3*(1+$AA$1)),
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), X3<other_product1, x3<other_product2),
.92*HIGHEST_OTHER_PRODUCT,
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), OR(X3>OTHER_PRODUCT1, X3>OTHER_PRODUCT2),
.95*HIGHEST_OTHER_PRODUCT))),

IF($F3='Lookup data'!$A$5,
IF(X3>HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE),X3/1.1,
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), X3>OTHER_PRODUCT1, X3>OTHER_PRODUCT2),
MIN(HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE),X3*(1+$AA$1)),
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), X3<other_product1, x3<other_product2),
.92*HIGHEST_OTHER_PRODUCT,
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), OR(X3>OTHER_PRODUCT1, X3>OTHER_PRODUCT2),
.95*HIGHEST_OTHER_PRODUCT))),

IF($F3='Lookup data'!$A$6,
IF(X3>HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE),X3/1.1,
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), X3>OTHER_PRODUCT1, X3>OTHER_PRODUCT2),
MIN(HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE),X3*(1+$AA$1)),
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), X3<other_product1, x3<other_product2),
.92*HIGHEST_OTHER_PRODUCT,
IF(AND(X3<=HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE), OR(X3>OTHER_PRODUCT1, X3>OTHER_PRODUCT2),
.95*HIGHEST_OTHER_PRODUCT))))))

Is anyone else dizzy?

Anyway, here it is line-by-line, spaced out in the same way as I spaced that wreck of a formula above (as you can see, they repeat for each product. possible for-loop available for when there are more than just 3 products). This assumes that these products' types' price references are 1000 (from HLOOKUP($B3,'Lookup data'!$F$2:$L$3,2,FALSE))

If product = product A, Then...
If "Price 12" for the product, is > 1000, then display whatever the calculation is for that product in column Z
Else if the product is <= 1000 but greater than the other products, then display whatever's smaller; 1000 or
some other calculation using price 12.
</other_product1,></other_product1,></other_product1,>Else if the product is <= 1000 AND < TWO other products, then
display the most expensive's product's price times .92<other_product1, x3<other_product2),
<other_product1, x3<other_product2),
<other_product1, x3<other_product2),

Else if the product is <= 1000 AND < one other product, then
display that other product's price times .95

If product = product B, Then...
</other_product1,></other_product1,></other_product1,>If "Price 12" for the product, is > 1000, then display whatever the calculation is for that product in column Z
Else if the product is <= 1000 but greater than the other products, then display whatever's smaller; 1000 or
some other calculation using price 12.
Else if the product is <= 1000 AND < TWO other products, then
display the most expensive's product's price times .92
Else if the product is <= 1000 AND < one other product, then
display that other product's price times .95
<other_product1, x3<other_product2),
<other_product1, x3<other_product2),
<other_product1, x3<other_product2),

If product = product C, Then...
If "Price 12" for the product, is > 1000, then display whatever the calculation is for that product in column Z
</other_product1,></other_product1,></other_product1,>Else if the product is <= 1000 but greater than the other products, then display whatever's smaller; 1000 or
some other calculation using price 12.
Else if the product is <= 1000 AND < TWO other products, then
display the most expensive's product's price times .92
Else if the product is <= 1000 AND < one other product, then
display that other product's price times .95<other_product1, x3<other_product2),
<other_product1, x3<other_product2),
<other_product1, x3<other_product2),


Replace the "OTHER_PRODUCT1" and "OTHER_PRODUCT2" with some kind of reference to the other products' "price 12"'s.
Replace the "HIGHEST_OTHER_PRODUCT" with a reference to whichever product has the highest "price 12".

So... umm... what do you think?</other_product1,></other_product1,></other_product1,>

 

[scottlarock]

Thank you so much for the detailed reply.
I will try to use that information to make it work.

Have a nice day !

 

[memar_one]

Yea... considering there's a cap for the number of characters in a formula, you might just want to use VBA instead.