Adding IF to an existing formula

[davecoppins]

Hope someone can help

I have a progressive pricing calculation which works as the purchase price increases, the profit margin decreases.
I would like to add an "IF" function to this (unless there is something better) which looks at sales volume and adds an additional % off the selling price or reduces the margin depending on volume.
So: cost price is £10.00 so add a mark on of 150% but if sales volume is 50 this year add a mark on of 140% / 100 this year add 130% / 150 this year add 120% etc.

The markon calculation is currently =(LOOKUP(AH2,'Markon Value'!M$3:N$68)+1)*AH2

Thanks in advance

 

[mamady]

Hi Dear,

Create a new sheet with a table of the required % tiers based on the sales volume (make sure it is in ascending order), for example

Book1
	A	B
1	Sales	Mark-on
2	0	150%
3	50	140%
4	100	130%
5	150	120%
6	200	110%
7	250	100%
SalesMarkon

then, try the below formula in the calculation sheet under cell AE2

Book1
	AE
2	£ 0.11
Calculation

Cell Formulas
Range	Formula
AE2	AE2	=VLOOKUP(AI2,SalesMarkon!$A$1:$B$7,2,TRUE)*AH2

Let me know

 

[davecoppins]

Hi Mamady

Thanks for looking at this for me, not quite what is required

What I would like to acheive is the original or similar calculation to create the sales price as the selling price runs across 5 selling levels and each selling level will have a different sell price for the same item which is created by the "Markon Value" Worksheet (Trade1 = cost +75% / Trade2 = cost +85% / Trade3 = Cost + 95% / Trade4 = Cost + 100% / Retail = Cost + £120%) then (appologies - this is probably what I explained wrong in the first thread) calculate a discount based on sales volume:

Item number XXX123:
Net price = 10.00
Markon value Trade1 = 75%
Sale price = 17.50
(if rolling year sales are between 10 & 20 apply 2% discount to the sale price)
(if rolling year sales are between 21 & 50 apply 5% discount to the sale price)
(if rolling year sales are between 51 & 100 apply 7% discount to the sale price)
(if rolling year sales are between 101 & 200 apply 10% discount to the sale price) etc.

Then

Item number XXX123:
Net price = 10.00
Markon value Trade2 = 85%
Sale price = 18.50
(if rolling year sales are between 10 & 20 apply 2% discount to the sale price)
(if rolling year sales are between 21 & 50 apply 5% discount to the sale price)
(if rolling year sales are between 51 & 100 apply 7% discount to the sale price)
(if rolling year sales are between 101 & 200 apply 10% discount to the sale price) etc.

Hope that makes sense!!!

Thanks in advance

David

 

[mamady]

Hi David,

I am a little bit lost

If it is possible, please share a sample of your working sheet via XL2BB.

Otherwise, I think you can use a similar concept I shared in my previous post. Where after the lookup, you multiply the price by the discounted %.
In this case, the table would be

Book1
	A	B	C
1	From	To	Discount %
2	0	10	0%
3	11	20	-2%
4	21	50	-5%
5	51	100	-7%
6	101	200	-10%
Sheet1

and the formula will be something like

(LOOKUP(AH2,'Markon Value'!M$3:N$68)+1)*(AH2*VLOOKUP(AI2,Sheet1!$A$1:$C$6,3,TRUE)+AH2)

the vlookup is a quicker way than doing nested if statements.

Best Regards

 

[davecoppins]

Hi Mamady

That works perfectly

Many thanks for your help on this

Kind regards

David

 

[mamady]

Hi David

I'm glad I was able to help, cheers.

Best Regards