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,>