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

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

<TBODY>
</TBODY>

Tons of thanks in advance,
Scott

 

[scottlarock]

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)

Hi Neil,

I adapted the formula with the relevant product names & it works great for the very first value, but stops there.

This is what the formula looks like now, the only thing I switched are the product names, the rest is identical :

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

This is how far I got (I highlighted the values that should have changed in red throughout column E) :

	A	B	C	D	E
1	Cust Type	Country	Product	Hypothetical Price	Tweaked Price
2	Hosp	Country 1	Level 1	1,058	1056
3	Hosp	Country 1	Level 2	1,100	#VALUE!
4	Hosp	Country 1	Level 3	1,000	#VALUE!
5	Hosp	Country 2	Level 3		#VALUE!
6	Dist	Country 3	Level 1	1,100	#VALUE!
7	Dist	Country 3	Level 2	1,090	#VALUE!
8	Dist	Country 3	Level 3	900	#VALUE!
9	Dist	Country 4	Level 1	1,160	#VALUE!
10	Dist	Country 5	Level 2	1,090	#VALUE!
11	Dist	Country 6	Level 1		#VALUE!
12	Dist	Country 6	Level 2	1,150	#VALUE!
13	Dist	Country 7	Level 1	1,200	#VALUE!
14	Dist	Country 7	Level 2	1,300	#VALUE!
15	Dist	Country 7	Level 3	1,400	#VALUE!
16	Dist	Country 8	Level 1	1,400	#VALUE!
17	Dist	Country 8	Level 2	1,300	#N/A
18	Dist	Country 8	Level 3	1,200	#N/A

<COLGROUP><COL style="WIDTH: 48pt" width=64><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" span=2 width=129><TBODY>
</TBODY>

Take care,
Scott

 

[njimack]

I could be wrong, but I can't see a way of doing this with native functions. So I've written a UDF - it's not pretty but I think it gives the desired output.

You'll need to use an additional column somewhere (I've used G2:G4) to list all possible Products.

To use this function, copy the below code, activate your file, press Alt+F11 to display the Visual Basic Editor (VBE). From the Insert menu, select Module. Paste the code, then close the VBE using the cross at the top-right of the screen.

Function Hypothetical(Country As String, Product As String, Hyp As Integer, Countries As Range, Products As Range, Hypotheticals As Range, Product_List As Range)


Dim c As Range
Dim p As String


For Each c In Countries


    If c.Value = Country Then
        p = c.Offset(, 1)
        
            Select Case WorksheetFunction.Match(p, Product_List, 0) - 1 = WorksheetFunction.Match(Product, Product_List, 0)
                Case 1:
                Hypothetical = c.Offset(, 3) * 98 / 100
                Exit Function
                
                Case 2:
                Hypothetical = c.Offset(, 3) * 96 / 100
                Exit Function
            
                Case Else:
                Hypothetical = Hyp
                Exit Function
            
            End Select
        Else: Hypothetical = Hyp
    End If
Next c


End Function

Sheet2

*	A	B	C	D	E	F	G
1	Cust Type	Country	Product	Hypothetical Price	Tweaked Price	*	Products
2	Hosp	Country 1	Level 1	1,058	960.4	*	Level 1
3	Hosp	Country 1	Level 2	1,100	980	*	Level 2
4	Hosp	Country 1	Level 3	1,000	1000	*	Level 3
5	Hosp	Country 2	Level 3	*	0	*	*
6	Dist	Country 3	Level 1	1,100	864.36	*	*
7	Dist	Country 3	Level 2	1,090	882	*	*
8	Dist	Country 3	Level 3	900	900	*	*
9	Dist	Country 4	Level 1	1,160	1160	*	*
10	Dist	Country 5	Level 2	1,090	1090	*	*
11	Dist	Country 6	Level 1	*	1127	*	*
12	Dist	Country 6	Level 2	1,150	1150	*	*
13	Dist	Country 7	Level 1	1,200	1344.56	*	*
14	Dist	Country 7	Level 2	1,300	1372	*	*
15	Dist	Country 7	Level 3	1,400	1400	*	*
16	Dist	Country 8	Level 1	1,400	1152.48	*	*
17	Dist	Country 8	Level 2	1,300	1176	*	*
18	Dist	Country 8	Level 3	1,200	1200	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:98px;"><col style="width:14px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =Hypothetical(B2,C2,D2,B3:B$18,C3:C$18,D3:D$18,G$2:G$4) E3 =Hypothetical(B3,C3,D3,B4:B$18,C4:C$18,D4:D$18,G$2:G$4) E4 =Hypothetical(B4,C4,D4,B5:B$18,C5:C$18,D5:D$18,G$2:G$4) E5 =Hypothetical(B5,C5,D5,B6:B$18,C6:C$18,D6:D$18,G$2:G$4) E6 =Hypothetical(B6,C6,D6,B7:B$18,C7:C$18,D7:D$18,G$2:G$4) E7 =Hypothetical(B7,C7,D7,B8:B$18,C8:C$18,D8:D$18,G$2:G$4) E8 =Hypothetical(B8,C8,D8,B9:B$18,C9:C$18,D9:D$18,G$2:G$4) E9 =Hypothetical(B9,C9,D9,B10:B$18,C10:C$18,D10:D$18,G$2:G$4) E10 =Hypothetical(B10,C10,D10,B11:B$18,C11:C$18,D11:D$18,G$2:G$4) E11 =Hypothetical(B11,C11,D11,B12:B$18,C12:C$18,D12:D$18,G$2:G$4) E12 =Hypothetical(B12,C12,D12,B13:B$18,C13:C$18,D13:D$18,G$2:G$4) E13 =Hypothetical(B13,C13,D13,B14:B$18,C14:C$18,D14:D$18,G$2:G$4) E14 =Hypothetical(B14,C14,D14,B15:B$18,C15:C$18,D15:D$18,G$2:G$4) E15 =Hypothetical(B15,C15,D15,B16:B$18,C16:C$18,D16:D$18,G$2:G$4) E16 =Hypothetical(B16,C16,D16,B17:B$18,C17:C$18,D17:D$18,G$2:G$4) E17 =Hypothetical(B17,C17,D17,B18:B$18,C18:C$18,D18:D$18,G$2:G$4) E18 =Hypothetical(B18,C18,D18,B$18:B19,C$18:C19,D$18:D19,G$2:G$4) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[scottlarock]

Neil, Sir,

You are a killer buddy... That macro (to me at least) is unbelievable. It's strange that excel wouldn't offer any native functions to do that but hey...
Thank you so much for the insight & support !

How would you enhance that macaroni to add "Cust Type" as a condition just as you added "Country" as the primary condition ?
I would like for the rule to be applicable only if prices fall under the same Cust Type and Country.
I tried foolin around with the code but I couldn't get it to work properly... kept on getting errors.

Thanks a million Neil !

Scott

 

[scottlarock]

I could be wrong, but I can't see a way of doing this with native functions. So I've written a UDF - it's not pretty but I think it gives the desired output.

You'll need to use an additional column somewhere (I've used G2:G4) to list all possible Products.

To use this function, copy the below code, activate your file, press Alt+F11 to display the Visual Basic Editor (VBE). From the Insert menu, select Module. Paste the code, then close the VBE using the cross at the top-right of the screen.

Function Hypothetical(Country As String, Product As String, Hyp As Integer, Countries As Range, Products As Range, Hypotheticals As Range, Product_List As Range)


Dim c As Range
Dim p As String


For Each c In Countries


    If c.Value = Country Then
        p = c.Offset(, 1)
        
            Select Case WorksheetFunction.Match(p, Product_List, 0) - 1 = WorksheetFunction.Match(Product, Product_List, 0)
                Case 1:
                Hypothetical = c.Offset(, 3) * 98 / 100
                Exit Function
                
                Case 2:
                Hypothetical = c.Offset(, 3) * 96 / 100
                Exit Function
            
                Case Else:
                Hypothetical = Hyp
                Exit Function
            
            End Select
        Else: Hypothetical = Hyp
    End If
Next c


End Function

Sheet2

*	A	B	C	D	E	F	G
1	Cust Type	Country	Product	Hypothetical Price	Tweaked Price	*	Products
2	Hosp	Country 1	Level 1	1,058	960.4	*	Level 1
3	Hosp	Country 1	Level 2	1,100	980	*	Level 2
4	Hosp	Country 1	Level 3	1,000	1000	*	Level 3
5	Hosp	Country 2	Level 3	*		*	*
6	Dist	Country 3	Level 1	1,100	864.36	*	*
7	Dist	Country 3	Level 2	1,090	882	*	*
8	Dist	Country 3	Level 3	900	900	*	*
9	Dist	Country 4	Level 1	1,160	1160	*	*
10	Dist	Country 5	Level 2	1,090	1090	*	*
11	Dist	Country 6	Level 1	*	1127	*	*
12	Dist	Country 6	Level 2	1,150	1150	*	*
13	Dist	Country 7	Level 1	1,200	1344.56	*	*
14	Dist	Country 7	Level 2	1,300	1372	*	*
15	Dist	Country 7	Level 3	1,400	1400	*	*
16	Dist	Country 8	Level 1	1,400	1152.48	*	*
17	Dist	Country 8	Level 2	1,300	1176	*	*
18	Dist	Country 8	Level 3	1,200	1200	*	*

<TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula E2 =Hypothetical(B2,C2,D2,B3:B$18,C3:C$18,D3:D$18,G$2:G$4) E3 =Hypothetical(B3,C3,D3,B4:B$18,C4:C$18,D4:D$18,G$2:G$4) E4 =Hypothetical(B4,C4,D4,B5:B$18,C5:C$18,D5:D$18,G$2:G$4) E5 =Hypothetical(B5,C5,D5,B6:B$18,C6:C$18,D6:D$18,G$2:G$4) E6 =Hypothetical(B6,C6,D6,B7:B$18,C7:C$18,D7:D$18,G$2:G$4) E7 =Hypothetical(B7,C7,D7,B8:B$18,C8:C$18,D8:D$18,G$2:G$4) E8 =Hypothetical(B8,C8,D8,B9:B$18,C9:C$18,D9:D$18,G$2:G$4) E9 =Hypothetical(B9,C9,D9,B10:B$18,C10:C$18,D10:D$18,G$2:G$4) E10 =Hypothetical(B10,C10,D10,B11:B$18,C11:C$18,D11:D$18,G$2:G$4) E11 =Hypothetical(B11,C11,D11,B12:B$18,C12:C$18,D12:D$18,G$2:G$4) E12 =Hypothetical(B12,C12,D12,B13:B$18,C13:C$18,D13:D$18,G$2:G$4) E13 =Hypothetical(B13,C13,D13,B14:B$18,C14:C$18,D14:D$18,G$2:G$4) E14 =Hypothetical(B14,C14,D14,B15:B$18,C15:C$18,D15:D$18,G$2:G$4) E15 =Hypothetical(B15,C15,D15,B16:B$18,C16:C$18,D16:D$18,G$2:G$4) E16 =Hypothetical(B16,C16,D16,B17:B$18,C17:C$18,D17:D$18,G$2:G$4) E17 =Hypothetical(B17,C17,D17,B18:B$18,C18:C$18,D18:D$18,G$2:G$4) E18 =Hypothetical(B18,C18,D18,B$18:B19,C$18:C19,D$18:D19,G$2:G$4) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Hello Neil,

Is it possible to enhance this macro to simply add "Cust Type" as a condition just as you added "Country" as the primary condition ?
I would like for the rule to be applicable only if prices fall under the same Cust Type and Country.

Many thanks in advance.
Have a nice evening out in London !

Scott

 

[njimack]

Hello Neil,

Is it possible to enhance this macro to simply add "Cust Type" as a condition just as you added "Country" as the primary condition ?
I would like for the rule to be applicable only if prices fall under the same Cust Type and Country.

Many thanks in advance.
Have a nice evening out in London !

Scott

Added an extra condition to the code. Your formula is now: =Hypothetical(A2,B2,C2,D2,B3:B$18,C3:C$18,D3:D$18,G$2:G$4)

Function Hypothetical(Cust_Type As String, Country As String, Product As String, Hyp As Integer, Countries As Range, Products As Range, Hypotheticals As Range, Product_List As Range)

Dim c As Range
Dim p As String

For Each c In Countries

If c.Value = Country And c.Offset(, -1) = Cust_Type Then
p = c.Offset(, 1)

Select Case WorksheetFunction.Match(p, Product_List, 0) - 1 = WorksheetFunction.Match(Product, Product_List, 0)
Case 1:
Hypothetical = c.Offset(, 3) * 98 / 100
Exit Function

Case 2:
Hypothetical = c.Offset(, 3) * 96 / 100
Exit Function

Case Else:
Hypothetical = Hyp
Exit Function

End Select
Else: Hypothetical = Hyp
End If
Next c
End Function

Let me know if it works as expected.

 

[scottlarock]

Added an extra condition to the code. Your formula is now: =Hypothetical(A2,B2,C2,D2,B3:B$18,C3:C$18,D3:D$18,G$2:G$4)

Function Hypothetical(Cust_Type As String, Country As String, Product As String, Hyp As Integer, Countries As Range, Products As Range, Hypotheticals As Range, Product_List As Range)

Dim c As Range
Dim p As String

For Each c In Countries

If c.Value = Country And c.Offset(, -1) = Cust_Type Then
p = c.Offset(, 1)

Select Case WorksheetFunction.Match(p, Product_List, 0) - 1 = WorksheetFunction.Match(Product, Product_List, 0)
Case 1:
Hypothetical = c.Offset(, 3) * 98 / 100
Exit Function

Case 2:
Hypothetical = c.Offset(, 3) * 96 / 100
Exit Function

Case Else:
Hypothetical = Hyp
Exit Function

End Select
Else: Hypothetical = Hyp
End If
Next c
End Function

Let me know if it works as expected.

Hi Neil,

Thanks for the update, the previous macro worked perfectly whereas I'm getting an error for this one called : "Ambiguous name detected : Hypothetical"
I entered the updated formula across column E (Tweaked Price) : =Hypothetical(A2,B2,C2,D2,B3:B$18,C3:C$18,D3:D$18,G$2:G$4)

Any clue what could be the matter with the slightly updated code ?

Many thanks in advance for your help !

Scott

Cust Type	Country	Product	Hypothetical Price	Tweaked Price
Hosp	Country 1	Level 1	1,058	#NAME?		Level 1
Hosp	Country 1	Level 2	1,100	#NAME?		Level 2
Hosp	Country 1	Level 3	1,000	#NAME?		Level 3
Hosp	Country 2	Level 3	0	#NAME?
Dist	Country 3	Level 1	1,100	#NAME?
Dist	Country 3	Level 2	1,090	#NAME?
Dist	Country 3	Level 3	900	#NAME?
Dist	Country 4	Level 1	1,160	#NAME?
Dist	Country 5	Level 2	1,090	#NAME?
Dist	Country 6	Level 1	0	#NAME?
Dist	Country 6	Level 2	1,150	#NAME?
Dist	Country 7	Level 1	1,200	#NAME?
Dist	Country 7	Level 2	1,300	#NAME?
Dist	Country 7	Level 3	1,400	#NAME?
Dist	Country 8	Level 1	1,400	#NAME?
Dist	Country 8	Level 2	1,300	#NAME?
Dist	Country 8	Level 3	1,200	#NAME?

<COLGROUP><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" span=2 width=129><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY>
</TBODY>

 

[njimack]

Make sure your code only contains one function called Hypothetical. Delete all previous code I gave you, so that the latest function is the only one in the workbook.

 

[scottlarock]

Make sure your code only contains one function called Hypothetical. Delete all previous code I gave you, so that the latest function is the only one in the workbook.

You are a Magician - I solved it with one mouse click and everything came out clean. That's the deal Neil...
Do you have a good website (or other) that could help me understand macros and heavy formulas like the one your first gave me ?
I know there is plenty of info out there, but I was just wondering if you had any preference or good suggestions to share.

Thank you very much for your help & insight, and sorry for being such a newbie.

Scott

 

[njimack]

You are a Magician - I solved it with one mouse click and everything came out clean. That's the deal Neil...
Do you have a good website (or other) that could help me understand macros and heavy formulas like the one your first gave me ?
I know there is plenty of info out there, but I was just wondering if you had any preference or good suggestions to share.

Thank you very much for your help & insight, and sorry for being such a newbie.

Scott

Glad to be of assistance.

I think I've got more than 100 Excel-related websites in my favourites. You should try the MVP links mentioned in this post: http://www.mrexcel.com/forum/showthread.php?628649-Recommended-Add-ins-and-Links
Additionally, below are a few of the sites I regularly use:
http://andrewexcel.blogspot.co.uk/
http://datapigtechnologies.com/blog/
http://chandoo.org/wp/
http://spreadsheetpage.com/

 

[scottlarock]

Glad to be of assistance.

I think I've got more than 100 Excel-related websites in my favourites. You should try the MVP links mentioned in this post: http://www.mrexcel.com/forum/showthread.php?628649-Recommended-Add-ins-and-Links
Additionally, below are a few of the sites I regularly use:
http://andrewexcel.blogspot.co.uk/
http://datapigtechnologies.com/blog/
http://chandoo.org/wp/
http://spreadsheetpage.com/

Thanks for the links Neil, I'm looking through them as we speak.
Take care and hope to talk to you soon !
Enjoy the wkend.
Scott