Applying Discounts for Price Band

[ankrups]

Hi,

I have another challenge. I need to apply the % of the margin on certain products based on the price band that if falls. For example, I need to create a list of the price band (Refer to column A& B) of the item first as per below table. Then in Column D is my cost price. Then based on the band (column A&B) if the price falls within then price band then I need to apply the % of margin plus cost in Column E.

Can you please advise how to achieve this? Hope I made it clear.

 

[Larry Haydn]

Here is a long-winded VBA code:

Option Explicit
Option Compare Text

Sub Worksheet_Change(ByVal target As Range)
'===================================================
'   Action when worksheet elements changed
'   In this case, we will be watching the cell D2
'===================================================
Dim CP As Single    'Cost Price

    CP = Val(target)
    
    If Not (Application.Intersect(target, Range("D2")) Is Nothing) Then
        With target
            If Not .HasFormula Then
                Application.EnableEvents = False

                '=====================
                '   Do calculations
                '=====================
                If CP >= 151 Then
                    Range("E2") = CP + (CP * Range("C17"))
                    
                ElseIf CP >= 51 Then
                    Range("E2") = CP + (CP * Range("C7"))
                    
                ElseIf CP >= 41 Then
                    Range("E2") = CP + (CP * Range("C6"))
                    
                ElseIf CP >= 31 Then
                    Range("E2") = CP + (CP * Range("C5"))
                    
                ElseIf CP >= 21 Then
                    Range("E2") = CP + (CP * Range("C4"))
                    
                ElseIf CP >= 11 Then
                    Range("E2") = CP + (CP * Range("C3"))
                    
                ElseIf CP >= 1 Then
                    Range("E2") = CP + (CP * Range("C2"))
                    
                Else
                    Range("E2") = ""
                    
                End If
                
                Application.EnableEvents = True
            End If
        End With
    End If

End Sub

 

[ankrups]

Thanks Larry. do you have any other option using excel formula? Sorry I am not techy so don't know how to use VBA coding

 

[Larry Haydn]

OK, you will have to modify the solution a bit to use formulas.

ADD A % COLUMN
First, insert a column between Cost Price and Price.

Change the first value of Price Band Lower from 0 to 1 (cell A2).

FIND THE %MARGIN BASED ON COST PRICE
Next, insert the following formula in column E (%):
=IFS(D2>=A17,C17,D2>=A7,C7,D2>=A6,C6,D2>=A5,C5,D2>=A4,C4,D2>=A3,C3,D2>=A2,C2,D2<=0,"")
Start the formula with the highest Price Band.
The D2<=0,"" part is to prevent error if a negative value is entered for Cost Price.

CALCULATE THE PRICE
Insert the following formula in column F (Price):
=IFERROR(IF(D2="","",D2+(D2*E2)),"")

PROTECT THE CELLS THAT HAS FORMULA
Finally, always protect the cells that contain formula.

How to do that?

1. Select the cell that you WANT to enter data (in this case, cell D2 - Cost Price),
and remove the check mark from Locked.

2. Protect the worksheet
Right-click the worksheet tab, select Protect Sheet
Make sure the Select unlocked cells option is selected

Hope this helps.

Here is the final screen capture:

 

[Larry Haydn]

*** MODIFIED ANSWER ***

OK, you will have to modify the solution a bit to use formulas.

ADD A % COLUMN
First, insert a column between Cost Price and Price, name it %.
View attachment 24231

CHANGE THE FIRST VALUE FOR PRICE BAND LOWER
Change cell A2 from 0 to 1

FIND THE %MARGIN BASED ON COST PRICE
Insert the following formula in cell E2 (%):
=IFS(D2>=A17,C17,D2>=A7,C7,D2>=A6,C6,D2>=A5,C5,D2>=A4,C4,D2>=A3,C3,D2>=A2,C2,D2<=0,"")
Note that the formula is IFS and not IF
Start the formula with the highest Price Band.
The D2<=0,"" part is to prevent error if a negative value is entered for Cost Price.

CALCULATE THE PRICE
Insert the following formula in cell F2 (Price):
=IFERROR(IF(D2="","",D2+(D2*E2)),"")

PROTECT THE CELLS THAT HAS FORMULA
Finally, always protect the cells that contain formula.

How to do that?

1. Format the cell
Right-click the cell that you WANT to enter data (in this case, cell D2 - Cost Price),
click Format cells, and remove the check mark from Locked.
View attachment 24232

2. Protect the worksheet
Right-click the worksheet tab, select Protect Sheet
Make sure the Select unlocked cells option is selected

Hope this helps.

Here is the final screen capture:
View attachment 24233

 

[Dossfm0q]

Greetings
What about, Feedback PLZ

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Band upper	Band Lower	%	Cost P	P	XX
2	0	10	151	$20.00	$48.02	$76.22
3	11	20	141	$25.00	$30.00	$55.25
4	21	30	101	$30.00	$54.00	$84.30
5	31	40	81	$35.00	$458.00	$486.35
6	41	50	61	$40.00	$54.00	$86.40
7	51	60	61	$45.00	$54.00	$81.45
8	61	70	61	$50.00	$54.00	$84.50
9	71	80	61	$55.00	$54.00	$87.55
10	81	90	61	$60.00	$54.00	$90.60
11	91	100	61	$65.00	$54.00	$93.65
12	101	110	61	$70.00	$54.00	$96.70
13	111	120	61	$75.00	$54.00	$99.75
14	121	130	61	$80.00	$54.00	$102.80
15	131	140	61	$85.00	$54.00	$105.85
16	141	150	61	$90.00	$54.00	$108.90
17	151	160	35	$95.00	$54.00	$111.95
18	161	170	35	$100.00	$54.00	$115.00
19	171	180	35	$105.00	$54.00	$118.05
20	181	1000000	35	$110.00	$54.00	$121.10
Sheet1

Cell Formulas
Range	Formula
F2:F20	F2	=((INDEX($C$2:$C$20,AGGREGATE(14,6,(ROW($B$2:$B$20)-ROW($B$2)+1)*(D2>=$A$2:$A$20)*(D2<=$B$2:$B$20),1))%)*D2)+E2

 

[ankrups]

Greetings
What about, Feedback PLZ

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Band upper	Band Lower	%	Cost P	P	XX
2	0	10	151	$20.00	$48.02	$76.22
3	11	20	141	$25.00	$30.00	$55.25
4	21	30	101	$30.00	$54.00	$84.30
5	31	40	81	$35.00	$458.00	$486.35
6	41	50	61	$40.00	$54.00	$86.40
7	51	60	61	$45.00	$54.00	$81.45
8	61	70	61	$50.00	$54.00	$84.50
9	71	80	61	$55.00	$54.00	$87.55
10	81	90	61	$60.00	$54.00	$90.60
11	91	100	61	$65.00	$54.00	$93.65
12	101	110	61	$70.00	$54.00	$96.70
13	111	120	61	$75.00	$54.00	$99.75
14	121	130	61	$80.00	$54.00	$102.80
15	131	140	61	$85.00	$54.00	$105.85
16	141	150	61	$90.00	$54.00	$108.90
17	151	160	35	$95.00	$54.00	$111.95
18	161	170	35	$100.00	$54.00	$115.00
19	171	180	35	$105.00	$54.00	$118.05
20	181	1000000	35	$110.00	$54.00	$121.10
Sheet1

Cell Formulas
Range	Formula
F2:F20	F2	=((INDEX($C$2:$C$20,AGGREGATE(14,6,(ROW($B$2:$B$20)-ROW($B$2)+1)*(D2>=$A$2:$A$20)*(D2<=$B$2:$B$20),1))%)*D2)+E2

Apologies for late reply. thanks mate. It;s working.