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

scottlarock · Jul 21, 2012

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 · Jul 22, 2012

njimack said:
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) :
[TABLE="width: 450"]
<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>[TR]
[TD="class: xl90, width: 64, bgcolor: #595959"] [/TD]
[TD="class: xl91, width: 111, bgcolor: #595959"] A[/TD]
[TD="class: xl91, width: 86, bgcolor: #595959"] B[/TD]
[TD="class: xl91, width: 80, bgcolor: #595959"] C[/TD]
[TD="class: xl91, width: 129, bgcolor: #595959"] D[/TD]
[TD="class: xl91, width: 129, bgcolor: #595959"] E[/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]1[/TD]
[TD="class: xl98, width: 111, bgcolor: #4bacc6"]Cust Type[/TD]
[TD="class: xl98, width: 86, bgcolor: #4bacc6"]Country[/TD]
[TD="class: xl98, width: 80, bgcolor: #4bacc6"]Product[/TD]
[TD="class: xl92, width: 129, bgcolor: #ebf1de"]Hypothetical Price[/TD]
[TD="class: xl98, width: 129, bgcolor: #4bacc6"]Tweaked Price[/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]2[/TD]
[TD="class: xl99, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl99, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl99, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl93, bgcolor: transparent"]1,058[/TD]
[TD="class: xl103, bgcolor: transparent"]1056[/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]3[/TD]
[TD="class: xl100, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl100, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl100, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl94, bgcolor: transparent"]1,100[/TD]
[TD="class: xl104, bgcolor: #ff5050"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]4[/TD]
[TD="class: xl101, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl101, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl101, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl95, bgcolor: transparent"]1,000[/TD]
[TD="class: xl105, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]5[/TD]
[TD="class: xl102, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl102, bgcolor: transparent"]Country 2[/TD]
[TD="class: xl102, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl96, bgcolor: transparent"] [/TD]
[TD="class: xl106, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]6[/TD]
[TD="class: xl99, bgcolor: transparent"]Dist[/TD]
[TD="class: xl99, bgcolor: transparent"]Country 3[/TD]
[TD="class: xl99, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl93, bgcolor: transparent"]1,100[/TD]
[TD="class: xl107, bgcolor: #ff5050"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]7[/TD]
[TD="class: xl100, bgcolor: transparent"]Dist[/TD]
[TD="class: xl100, bgcolor: transparent"]Country 3[/TD]
[TD="class: xl100, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl94, bgcolor: transparent"]1,090[/TD]
[TD="class: xl104, bgcolor: #ff5050"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]8[/TD]
[TD="class: xl101, bgcolor: transparent"]Dist[/TD]
[TD="class: xl101, bgcolor: transparent"]Country 3[/TD]
[TD="class: xl101, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl95, bgcolor: transparent"]900[/TD]
[TD="class: xl105, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]9[/TD]
[TD="class: xl102, bgcolor: transparent"]Dist[/TD]
[TD="class: xl102, bgcolor: transparent"]Country 4[/TD]
[TD="class: xl102, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl96, bgcolor: transparent"]1,160[/TD]
[TD="class: xl106, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]10[/TD]
[TD="class: xl102, bgcolor: transparent"]Dist[/TD]
[TD="class: xl102, bgcolor: transparent"]Country 5[/TD]
[TD="class: xl102, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl96, bgcolor: transparent"]1,090[/TD]
[TD="class: xl106, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]11[/TD]
[TD="class: xl99, bgcolor: transparent"]Dist[/TD]
[TD="class: xl99, bgcolor: transparent"]Country 6[/TD]
[TD="class: xl99, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl93, bgcolor: transparent"] [/TD]
[TD="class: xl103, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]12[/TD]
[TD="class: xl101, bgcolor: transparent"]Dist[/TD]
[TD="class: xl101, bgcolor: transparent"]Country 6[/TD]
[TD="class: xl101, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl95, bgcolor: transparent"]1,150[/TD]
[TD="class: xl105, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]13[/TD]
[TD="class: xl99, bgcolor: transparent"]Dist[/TD]
[TD="class: xl99, bgcolor: transparent"]Country 7[/TD]
[TD="class: xl99, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl93, bgcolor: transparent"]1,200[/TD]
[TD="class: xl103, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]14[/TD]
[TD="class: xl100, bgcolor: transparent"]Dist[/TD]
[TD="class: xl100, bgcolor: transparent"]Country 7[/TD]
[TD="class: xl100, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl94, bgcolor: transparent"]1,300[/TD]
[TD="class: xl108, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]15[/TD]
[TD="class: xl101, bgcolor: transparent"]Dist[/TD]
[TD="class: xl101, bgcolor: transparent"]Country 7[/TD]
[TD="class: xl101, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl95, bgcolor: transparent"]1,400[/TD]
[TD="class: xl105, bgcolor: transparent"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]16[/TD]
[TD="class: xl99, bgcolor: transparent"]Dist[/TD]
[TD="class: xl99, bgcolor: transparent"]Country 8[/TD]
[TD="class: xl99, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl93, bgcolor: transparent"]1,400[/TD]
[TD="class: xl104, bgcolor: #ff5050"]#VALUE![/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]17[/TD]
[TD="class: xl100, bgcolor: transparent"]Dist[/TD]
[TD="class: xl100, bgcolor: transparent"]Country 8[/TD]
[TD="class: xl100, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl94, bgcolor: transparent"]1,300[/TD]
[TD="class: xl104, bgcolor: #ff5050"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #595959"]18[/TD]
[TD="class: xl101, bgcolor: transparent"]Dist[/TD]
[TD="class: xl101, bgcolor: transparent"]Country 8[/TD]
[TD="class: xl101, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl95, bgcolor: transparent"]1,200[/TD]
[TD="class: xl105, bgcolor: transparent"]#N/A[/TD]
[/TR]
</TBODY>[/TABLE]

Take care,
Scott

njimack · Jul 22, 2012

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.

Code:

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
Cust Type	Country	Product	Hypothetical Price	Tweaked Price	*	Products
Hosp	Country 1	Level 1	*
Hosp	Country 1	Level 2	*
Hosp	Country 1	Level 3	*
Hosp	Country 2	Level 3	*	*	*
Dist	Country 3	Level 1	*	*
Dist	Country 3	Level 2	*	*
Dist	Country 3	Level 3	*	*
Dist	Country 4	Level 1	*	*
Dist	Country 5	Level 2	*	*
Dist	Country 6	Level 1	*	*	*
Dist	Country 6	Level 2	*	*
Dist	Country 7	Level 1	*	*
Dist	Country 7	Level 2	*	*
Dist	Country 7	Level 3	*	*
Dist	Country 8	Level 1	*	*
Dist	Country 8	Level 2	*	*
Dist	Country 8	Level 3	*	*

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1,058[/TD]
[TD="align: right"]960.4[/TD]

[TD="bgcolor: #ffff00"]Level 1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1,100[/TD]
[TD="align: right"]980[/TD]

[TD="bgcolor: #ffff00"]Level 2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1,000[/TD]
[TD="align: right"]1000[/TD]

[TD="bgcolor: #ffff00"]Level 3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1,100[/TD]
[TD="align: right"]864.36[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]1,090[/TD]
[TD="align: right"]882[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1,160[/TD]
[TD="align: right"]1160[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]1,090[/TD]
[TD="align: right"]1090[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]1127[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]1,150[/TD]
[TD="align: right"]1150[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]1,200[/TD]
[TD="align: right"]1344.56[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]1,300[/TD]
[TD="align: right"]1372[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]1,400[/TD]
[TD="align: right"]1400[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]1,400[/TD]
[TD="align: right"]1152.48[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]1,300[/TD]
[TD="align: right"]1176[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]1,200[/TD]
[TD="align: right"]1200[/TD]

</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)

Excel tables to the web >> Excel Jeanie HTML 4

scottlarock · Jul 23, 2012

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 · Jul 26, 2012

njimack said:
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.

Code:

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

Cust Type
Country
Product
Hypothetical Price
Tweaked Price
*
Products

Hosp
Country 1
Level 1
*

Hosp
Country 1
Level 2
*

Hosp
Country 1
Level 3
*

Hosp
Country 2
Level 3
*
*
*

Dist
Country 3
Level 1
*
*

Dist
Country 3
Level 2
*
*

Dist
Country 3
Level 3
*
*

Dist
Country 4
Level 1
*
*

Dist
Country 5
Level 2
*
*

Dist
Country 6
Level 1
*
*
*

Dist
Country 6
Level 2
*
*

Dist
Country 7
Level 1
*
*

Dist
Country 7
Level 2
*
*

Dist
Country 7
Level 3
*
*

Dist
Country 8
Level 1
*
*

Dist
Country 8
Level 2
*
*

Dist
Country 8
Level 3
*
*

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="align: right"]1,058
[/TD]
[TD="align: right"]960.4
[/TD]

[TD="bgcolor: #ffff00"]Level 1
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

[TD="align: right"]1,100
[/TD]
[TD="align: right"]980
[/TD]

[TD="bgcolor: #ffff00"]Level 2
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]

[TD="align: right"]1,000
[/TD]
[TD="align: right"]1000
[/TD]

[TD="bgcolor: #ffff00"]Level 3
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #cacaca"]6
[/TD]

[TD="align: right"]1,100
[/TD]
[TD="align: right"]864.36
[/TD]

[TD="bgcolor: #cacaca"]7
[/TD]

[TD="align: right"]1,090
[/TD]
[TD="align: right"]882
[/TD]

[TD="bgcolor: #cacaca"]8
[/TD]

[TD="align: right"]900
[/TD]
[TD="align: right"]900
[/TD]

[TD="bgcolor: #cacaca"]9
[/TD]

[TD="align: right"]1,160
[/TD]
[TD="align: right"]1160
[/TD]

[TD="bgcolor: #cacaca"]10
[/TD]

[TD="align: right"]1,090
[/TD]
[TD="align: right"]1090
[/TD]

[TD="bgcolor: #cacaca"]11
[/TD]

[TD="align: right"]1127
[/TD]

[TD="bgcolor: #cacaca"]12
[/TD]

[TD="align: right"]1,150
[/TD]
[TD="align: right"]1150
[/TD]

[TD="bgcolor: #cacaca"]13
[/TD]

[TD="align: right"]1,200
[/TD]
[TD="align: right"]1344.56
[/TD]

[TD="bgcolor: #cacaca"]14
[/TD]

[TD="align: right"]1,300
[/TD]
[TD="align: right"]1372
[/TD]

[TD="bgcolor: #cacaca"]15
[/TD]

[TD="align: right"]1,400
[/TD]
[TD="align: right"]1400
[/TD]

[TD="bgcolor: #cacaca"]16
[/TD]

[TD="align: right"]1,400
[/TD]
[TD="align: right"]1152.48
[/TD]

[TD="bgcolor: #cacaca"]17
[/TD]

[TD="align: right"]1,300
[/TD]
[TD="align: right"]1176
[/TD]

[TD="bgcolor: #cacaca"]18
[/TD]

[TD="align: right"]1,200
[/TD]
[TD="align: right"]1200
[/TD]

</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 · Jul 26, 2012

scottlarock said:
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)

Code:

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 · Jul 27, 2012

njimack said:

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)

Code:

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
[TABLE="width: 473"]
<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>[TR]
[TD="class: xl92, width: 111, bgcolor: #4bacc6"]Cust Type[/TD]
[TD="class: xl92, width: 86, bgcolor: #4bacc6"]Country[/TD]
[TD="class: xl92, width: 80, bgcolor: #4bacc6"]Product[/TD]
[TD="class: xl86, width: 129, bgcolor: #ebf1de"]Hypothetical Price[/TD]
[TD="class: xl92, width: 129, bgcolor: #4bacc6"]Tweaked Price[/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl93, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl93, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl93, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl87, bgcolor: transparent"]1,058[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Level 1[/TD]
[/TR]
[TR]
[TD="class: xl94, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl94, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl94, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl88, bgcolor: transparent"]1,100[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Level 2[/TD]
[/TR]
[TR]
[TD="class: xl95, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl95, bgcolor: transparent"]Country 1[/TD]
[TD="class: xl95, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl89, bgcolor: transparent"]1,000[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Level 3[/TD]
[/TR]
[TR]
[TD="class: xl96, bgcolor: transparent"]Hosp[/TD]
[TD="class: xl96, bgcolor: transparent"]Country 2[/TD]
[TD="class: xl96, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl90, bgcolor: transparent"]0[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl93, bgcolor: transparent"]Dist[/TD]
[TD="class: xl93, bgcolor: transparent"]Country 3[/TD]
[TD="class: xl93, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl87, bgcolor: transparent"]1,100[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl94, bgcolor: transparent"]Dist[/TD]
[TD="class: xl94, bgcolor: transparent"]Country 3[/TD]
[TD="class: xl94, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl88, bgcolor: transparent"]1,090[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl95, bgcolor: transparent"]Dist[/TD]
[TD="class: xl95, bgcolor: transparent"]Country 3[/TD]
[TD="class: xl95, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl89, bgcolor: transparent"]900[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl96, bgcolor: transparent"]Dist[/TD]
[TD="class: xl96, bgcolor: transparent"]Country 4[/TD]
[TD="class: xl96, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl90, bgcolor: transparent"]1,160[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl96, bgcolor: transparent"]Dist[/TD]
[TD="class: xl96, bgcolor: transparent"]Country 5[/TD]
[TD="class: xl96, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl90, bgcolor: transparent"]1,090[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl93, bgcolor: transparent"]Dist[/TD]
[TD="class: xl93, bgcolor: transparent"]Country 6[/TD]
[TD="class: xl93, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl87, bgcolor: transparent"]0[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl95, bgcolor: transparent"]Dist[/TD]
[TD="class: xl95, bgcolor: transparent"]Country 6[/TD]
[TD="class: xl95, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl89, bgcolor: transparent"]1,150[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl93, bgcolor: transparent"]Dist[/TD]
[TD="class: xl93, bgcolor: transparent"]Country 7[/TD]
[TD="class: xl93, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl87, bgcolor: transparent"]1,200[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl94, bgcolor: transparent"]Dist[/TD]
[TD="class: xl94, bgcolor: transparent"]Country 7[/TD]
[TD="class: xl94, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl88, bgcolor: transparent"]1,300[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl95, bgcolor: transparent"]Dist[/TD]
[TD="class: xl95, bgcolor: transparent"]Country 7[/TD]
[TD="class: xl95, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl89, bgcolor: transparent"]1,400[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl93, bgcolor: transparent"]Dist[/TD]
[TD="class: xl93, bgcolor: transparent"]Country 8[/TD]
[TD="class: xl93, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl87, bgcolor: transparent"]1,400[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl94, bgcolor: transparent"]Dist[/TD]
[TD="class: xl94, bgcolor: transparent"]Country 8[/TD]
[TD="class: xl94, bgcolor: transparent"]Level 2[/TD]
[TD="class: xl88, bgcolor: transparent"]1,300[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl95, bgcolor: transparent"]Dist[/TD]
[TD="class: xl95, bgcolor: transparent"]Country 8[/TD]
[TD="class: xl95, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl89, bgcolor: transparent"]1,200[/TD]
[TD="class: xl97, bgcolor: transparent"]#NAME?[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

njimack · Jul 27, 2012

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 · Jul 27, 2012

njimack said:
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 · Jul 27, 2012

scottlarock said:
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 · Jul 27, 2012

njimack said:
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

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

scottlarock

Board Regular

scottlarock

Board Regular

Excel Facts

njimack

Well-known Member

scottlarock

Board Regular

scottlarock

Board Regular

njimack

Well-known Member

scottlarock

Board Regular

njimack

Well-known Member

scottlarock

Board Regular

njimack

Well-known Member

scottlarock

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

Board Regular

Excel Facts

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock