if higher number within a given set of matching criteria is found, calculate new value based on highest, and or second highest

[scottlarock]

Hello,

I have been trying to get an answer to this, I'll try again.

I have 3 product types in column F, "level 1", "level 2", "level 3".
If, there is more than one product type sharing the same country (C) and Cust type (B), and "level 1" price is higher than either "level 2" or "level 3",

--> then "level 1" price should be 2% less than "level 2" (if there is such a product sharing the same country & cust type), and 4% less than "level 3" (if there is such a product sharing the same country & cust type).

Similarly, if there is a "level 2" with a higher price than a "level 3" within the same country (C) & cust type (B),

--> then "level 2" price should be 2% less than "level 3".

In this example, Z24 should be 4% less than Z26.

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

[TABLE="class: cms_table_cms_table"]
<TBODY>[TR]
[TD="class: cms_table_cms_table_xl96"][/TD]
[TD="class: cms_table_cms_table_xl96, width: 111, bgcolor: #404040"]A
[/TD]
[TD="class: cms_table_cms_table_xl96, width: 111, bgcolor: #404040"]B
[/TD]
[TD="class: cms_table_cms_table_xl96, width: 86, bgcolor: #404040"]C
[/TD]
[TD="class: cms_table_cms_table_xl96, width: 80, bgcolor: #404040"]F
[/TD]
[TD="class: cms_table_cms_table_xl96, width: 78, bgcolor: #404040"]X
[/TD]
[TD="class: cms_table_cms_table_xl96, width: 85, bgcolor: #404040"]Y
[/TD]
[TD="class: cms_table_cms_table_xl96, width: 129, bgcolor: #404040"]Z
[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl96, bgcolor: #404040"]1
[/TD]
[TD="class: cms_table_cms_table_xl89, width: 111, bgcolor: #4bacc6"]Region
[/TD]
[TD="class: cms_table_cms_table_xl89, width: 111, bgcolor: #4bacc6"]Cust Type
[/TD]
[TD="class: cms_table_cms_table_xl89, width: 86, bgcolor: #4bacc6"]Country
[/TD]
[TD="class: cms_table_cms_table_xl89, width: 80, bgcolor: #4bacc6"]Product
[/TD]
[TD="class: cms_table_cms_table_xl90, width: 78, bgcolor: #ebf1de"]Price
[/TD]
[TD="class: cms_table_cms_table_xl90, width: 85, bgcolor: #ebf1de"]Quantity
[/TD]
[TD="class: cms_table_cms_table_xl91, width: 129, bgcolor: #ebf1de"]Hypothetical D Price
[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl96, bgcolor: #404040"]24
[/TD]
[TD="bgcolor: transparent"]Reg 1
[/TD]
[TD="class: cms_table_cms_table_xl88, bgcolor: transparent"]Hosp
[/TD]
[TD="bgcolor: transparent"]Cntry 1
[/TD]
[TD="class: cms_table_cms_table_xl95, bgcolor: #ffffcc"]level 1
[/TD]
[TD="class: cms_table_cms_table_xl94, bgcolor: transparent"]920
[/TD]
[TD="class: cms_table_cms_table_xl94, bgcolor: transparent"]88
[/TD]
[TD="class: cms_table_cms_table_xl93, bgcolor: transparent"]1,058.2
[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl96, bgcolor: #404040"]25
[/TD]
[TD="bgcolor: transparent"]Reg 1
[/TD]
[TD="class: cms_table_cms_table_xl88, bgcolor: transparent"]Hosp
[/TD]
[TD="bgcolor: transparent"]Cntry 1
[/TD]
[TD="class: cms_table_cms_table_xl95, bgcolor: #ffffcc"]level 2
[/TD]
[TD="class: cms_table_cms_table_xl94, bgcolor: transparent"][/TD]
[TD="class: cms_table_cms_table_xl94, bgcolor: transparent"]2
[/TD]
[TD="class: cms_table_cms_table_xl93, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_xl96, bgcolor: #404040"]26
[/TD]
[TD="bgcolor: transparent"]Reg 1
[/TD]
[TD="class: cms_table_cms_table_xl88, bgcolor: transparent"]Hosp
[/TD]
[TD="bgcolor: transparent"]Cntry 1
[/TD]
[TD="class: cms_table_cms_table_xl95, bgcolor: #ffffcc"]level 3
[/TD]
[TD="class: cms_table_cms_table_xl94, bgcolor: transparent"]1,106
[/TD]
[TD="class: cms_table_cms_table_xl94, bgcolor: transparent"]0
[/TD]
[TD="class: cms_table_cms_table_xl93, bgcolor: transparent"]1,005.5
[/TD]
[/TR]
</TBODY>[/TABLE]


Tons of thanks in advance,
Scott

 

[scottlarock]

Small amendment to make things clearer, when I refer to prices, I mean Hypothetical prices.

Hello,

I have been trying to get an answer to this, I'll try again.

I have 3 product types in column F, "level 1", "level 2", "level 3".
If, there is more than one product type sharing the same country (C) and Cust type (B), and "level 1" hypothetical price (Z) is higher than either "level 2" or "level 3" hypothetical prices (Z),

--> then "level 1" hypothetical price (Z) should be 2% less than "level 2" hypothetical price (Z) (if there is such a product sharing the same country & cust type), and 4% less than "level 3" hypothetical price (Z) (if there is such a product sharing the same country & cust type).

Similarly, if there is a "level 2" with a higher hypothetical price (Z) than a potential "level 3" hypothetical price (Z) within the same country (C) & cust type (B),

--> then "level 2" hypothetical price (Z) price should be 2% less than "level 3" hypothetical price (Z).

In this example, Z24 should be 4% less than Z26.

Any clue what the perfect formula would look like ? :°)
[TABLE="class: cms_table_cms_table_cms_table"]
<TBODY>[TR]
[TD="class: cms_table_cms_table_cms_table_xl96"][/TD]
[TD="class: cms_table_cms_table_cms_table_xl96, width: 111, bgcolor: #404040"]A[/TD]
[TD="class: cms_table_cms_table_cms_table_xl96, width: 111, bgcolor: #404040"]B[/TD]
[TD="class: cms_table_cms_table_cms_table_xl96, width: 86, bgcolor: #404040"]C[/TD]
[TD="class: cms_table_cms_table_cms_table_xl96, width: 80, bgcolor: #404040"]F[/TD]
[TD="class: cms_table_cms_table_cms_table_xl96, width: 78, bgcolor: #404040"]X[/TD]
[TD="class: cms_table_cms_table_cms_table_xl96, width: 85, bgcolor: #404040"]Y[/TD]
[TD="class: cms_table_cms_table_cms_table_xl96, width: 129, bgcolor: #404040"]Z[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_cms_table_xl96, bgcolor: #404040"]1[/TD]
[TD="class: cms_table_cms_table_cms_table_xl89, width: 111, bgcolor: #4bacc6"]Region[/TD]
[TD="class: cms_table_cms_table_cms_table_xl89, width: 111, bgcolor: #4bacc6"]Cust Type[/TD]
[TD="class: cms_table_cms_table_cms_table_xl89, width: 86, bgcolor: #4bacc6"]Country[/TD]
[TD="class: cms_table_cms_table_cms_table_xl89, width: 80, bgcolor: #4bacc6"]Product[/TD]
[TD="class: cms_table_cms_table_cms_table_xl90, width: 78, bgcolor: #ebf1de"]Price[/TD]
[TD="class: cms_table_cms_table_cms_table_xl90, width: 85, bgcolor: #ebf1de"]Quantity[/TD]
[TD="class: cms_table_cms_table_cms_table_xl91, width: 129, bgcolor: #ebf1de"]Hypothetical D Price[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_cms_table_xl96, bgcolor: #404040"]24[/TD]
[TD="bgcolor: transparent"]Reg 1[/TD]
[TD="class: cms_table_cms_table_cms_table_xl88, bgcolor: transparent"]Hosp[/TD]
[TD="bgcolor: transparent"]Cntry 1[/TD]
[TD="class: cms_table_cms_table_cms_table_xl95, bgcolor: #ffffcc"]level 1[/TD]
[TD="class: cms_table_cms_table_cms_table_xl94, bgcolor: transparent"]920[/TD]
[TD="class: cms_table_cms_table_cms_table_xl94, bgcolor: transparent"]88[/TD]
[TD="class: cms_table_cms_table_cms_table_xl93, bgcolor: transparent"]1,058.2[/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_cms_table_xl96, bgcolor: #404040"]25[/TD]
[TD="bgcolor: transparent"]Reg 1[/TD]
[TD="class: cms_table_cms_table_cms_table_xl88, bgcolor: transparent"]Hosp[/TD]
[TD="bgcolor: transparent"]Cntry 1[/TD]
[TD="class: cms_table_cms_table_cms_table_xl95, bgcolor: #ffffcc"]level 2[/TD]
[TD="class: cms_table_cms_table_cms_table_xl94, bgcolor: transparent"][/TD]
[TD="class: cms_table_cms_table_cms_table_xl94, bgcolor: transparent"]2[/TD]
[TD="class: cms_table_cms_table_cms_table_xl93, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_cms_table_cms_table_xl96, bgcolor: #404040"]26[/TD]
[TD="bgcolor: transparent"]Reg 1[/TD]
[TD="class: cms_table_cms_table_cms_table_xl88, bgcolor: transparent"]Hosp[/TD]
[TD="bgcolor: transparent"]Cntry 1[/TD]
[TD="class: cms_table_cms_table_cms_table_xl95, bgcolor: #ffffcc"]level 3[/TD]
[TD="class: cms_table_cms_table_cms_table_xl94, bgcolor: transparent"]1,106[/TD]
[TD="class: cms_table_cms_table_cms_table_xl94, bgcolor: transparent"]0[/TD]
[TD="class: cms_table_cms_table_cms_table_xl93, bgcolor: transparent"]1,005.5[/TD]
[/TR]
</TBODY>[/TABLE]

 

[njimack]

Your sample data doesn't match your description - Level 1 price is not ​higher than Level 3 price.

 

[njimack]

What result would you want if both conditions are true, i.e Level 2 price and Level 3 price are both higher than Level 1?

 

[scottlarock]

Hi neil,
Yep... Hence the amendment in my auto reply above.
The column of focus is "hypothetical d price" in column z.

Have any suggestions ?
Thanks,
Scott

 

[scottlarock]

What result would you want if both conditions are true, i.e Level 2 price and Level 3 price are both higher than Level 1?

Neil,

Thank you so much for takin a look at this dilemna.

First off, all three product types (level 1, level 2, level 3) are not systematically there. the formula should apply in the instances that there is more than one product type within the same country & cust type) where the lower levels have a higher hypothetical price.

In any case, I have two answers for you.

Number 1 :
If either condition is true, i.e : either "level 2" hypothetical price (should there be one in the given country & cust type) and or "level 3" hypothetical price (should there be one matching the country and cust type) are higher than "level 1" hypothetical price,
--> then " ".

Number 2 :
If either condition is true, i.e : either "level 2" hypothetical price and or "level 3" hypothetical price are higher than "level 1" hypothetical price,
--> then "level 1" hypothetical price should be 2% lower than "level 2" hypothetical price (should there be one matching the country and cust type), which in turn should be 2% lower than "level 3" hypothetical price (should there be one matching the country and cust type).

Tough to explain...but to make it simple... level 1 is supposed to be cheaper than level 2 which in turn is supposed to be cheaper than level 3.

Hope you can help me out !
scott

 

[njimack]

This should give you the desired result for Levels 1 and 2, but you haven't said how you would arrive at the Level 3 hypothetical price...

=IF(Y2,MAX(SUMPRODUCT(--(C$3:C4=C2),--(RIGHT(F$3:F4)-1=RIGHT(F2)+0),(Z$3:Z4))*98%,SUMPRODUCT(--(C$3:C4=C2),--(RIGHT(F$3:F4)-2=RIGHT(F2)+0),(Z$3:Z4))*96%),0)

 

[scottlarock]

This should give you the desired result for Levels 1 and 2, but you haven't said how you would arrive at the Level 3 hypothetical price...

=IF(Y2,MAX(SUMPRODUCT(--(C$3:C4=C2),--(RIGHT(F$3:F4)-1=RIGHT(F2)+0),(Z$3:Z4))*98%,SUMPRODUCT(--(C$3:C4=C2),--(RIGHT(F$3:F4)-2=RIGHT(F2)+0),(Z$3:Z4))*96%),0)

Hi Neil,

I haven't tried the formula just yet but it looks like it will do the trick.
From reading your reply, I realized that the data set looked confusing. I saw you were using column Y (quantity) from my initial table above, which I am not at all concerned with at this point....I would only like to tweak the hypothetical prices (this time around in column D to make it simple).
I will try to explain again real quick. I already have a formula calculating the hypothetical price based on the original price with a few conditions (nothing fancy), but the prices need fine tuning.
As you understood it, "level 1" hypothetical price should be 2% less than "level 2" hypothetical price, or 4% less than "level 3" hypothetical price (if there is such a product within the same country and same cust type)

Similarly, "level 2" hypothetical price should be 2% cheaper than "level 3" (if there is such a product within the same country and same cust type).
To answer your comment about "level 3", its hypothetical price will stay as it is, no need to tweak it.

Once I get the magic formula, which I think you pretty much sorted out already, I will try to incorporate it within the existing one, or work on another column.
I have pasted below only the data I need to be evaluated (no useless other columns like I had beforehand) :

I do apologize if I was unclear in anyway, and hope that you will be able to help me out.
[TABLE="width: 388"]
<COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><TBODY>[TR]
[TD="class: xl96, width: 111, bgcolor: #404040"] [/TD]
[TD="class: xl96, width: 111, bgcolor: #404040"] A [/TD]
[TD="class: xl96, width: 86, bgcolor: #404040"] B[/TD]
[TD="class: xl96, width: 80, bgcolor: #404040"] C[/TD]
[TD="class: xl96, width: 129, bgcolor: #404040"] D[/TD]
[/TR]
[TR]
[TD="class: xl96, bgcolor: #404040"]1[/TD]
[TD="class: xl89, width: 111, bgcolor: #4bacc6"]Cust Type[/TD]
[TD="class: xl89, width: 86, bgcolor: #4bacc6"]Country[/TD]
[TD="class: xl89, width: 80, bgcolor: #4bacc6"]Code[/TD]
[TD="class: xl91, width: 129, bgcolor: #ebf1de"]Hypothetical Price[/TD]
[/TR]
[TR]
[TD="class: xl96, bgcolor: #404040"]2[/TD]
[TD="class: xl88, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl97, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl98, bgcolor: #ffffcc"]Level 1[/TD]
[TD="class: xl100, bgcolor: transparent"]1,058.2[/TD]
[/TR]
[TR]
[TD="class: xl96, bgcolor: #404040"]3[/TD]
[TD="class: xl88, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl101, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl95, bgcolor: #ffffcc"]Level 2[/TD]
[TD="class: xl93, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl96, bgcolor: #404040"]4[/TD]
[TD="class: xl88, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl102, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl103, bgcolor: #ffffcc"]Level 3[/TD]
[TD="class: xl105, bgcolor: transparent"]1,005.5[/TD]
[/TR]
</TBODY>[/TABLE]


Tons of thanks,
Scott

 

[scottlarock]

By the way, the right function won't work as the products have actual names, they are not actually called level 1,2,3.
Thx,
Scott

 

[njimack]

Using the latest layout you posted (columns A:D), and taking account of Product names without numbers, give this a go in column E. You'll need to change the product names I've hard-coded into the array, or enter them into a separate area of your sheet and link to them...

=IF(D2,MAX(SUMPRODUCT(--(B$3:B4=B2),--(MATCH(C3:C4,{"Level 1","Level 2","Level 3"},0)-1=MATCH(C3:C4,{"Level 1","Level 2","Level 3"},0)),(D$3:D4))*98%,SUMPRODUCT(--(B$3:B4=B2),MATCH(C3:C4,{"Level 1","Level 2","Level 3"},0)=MATCH(C3:C4,{"Level 1","Level 2","Level 3"},0)),(D$3:D4))*96%,0)