If statement less than more than and margin

[duck90]

I've got a query that's too complex for me to solve...

I have a pricing matrix as follows and want a formula to automatically work out a price.

I have a cost price list such as:

£8, £12, £20, £36, £39, £55 etc.

£0-£5 we add 40p then x240%
£5-£8 we add 50p then x220%
£8-£10 we add 60p then x200%

This goes on but I can only get the first result working.

I want to be able to add a cost price in my cost column, and the sale price automatically result. Ie cost price £8.11, results in retail price being £26.13.

Any geniuses able to help me?

 

[Alex Blakenburg]

I would be making the pricing table an actual Excel Table so that it automatially expands but try this.

Book7
	B	C	D	E	F	G	H	I	J	K
1	Net Cost	Retail			Original Text		From	To	Add	PerCent
2	8.11	26.13			£0-£5 we add 40p then x240%		0	5	0.4	240%
3	7.49	25.568			£5-£8 we add 50p then x220%		5	8	0.5	220%
4					£8-£10 we add 60p then x200%		8	10	0.6	200%
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=(B2+VLOOKUP(B2,$H$2:$K$4,3,TRUE))*(1+VLOOKUP(B2,$H$2:$K$4,4,TRUE))

 

[AlanY]

try this, the lookup table is arranged to desending order to work.

TT.xlsx
	A	B	C	D	E	F
1	8.11	17.42		99	15	20%
2	11.99	4.158		40	14	20%
3	11.54	4.068		35	13	20%
4	12.66	4.292		30	12	20%
5	25.5	7.5		25	11	20%
6	33.75	9.35		20	10	20%
7	45	12		15	8.8	20%
8				10	0.6	200%
9				8	0.5	220%
10				5	0.4	240%
Sheet4

Cell Formulas
Range	Formula
B1:B7	B1	=(A1+INDEX($D$1:$F$10,MATCH(A1*1.01,$D$1:$D$10,-1),2))*INDEX($D$1:$F$10,MATCH(A1*1.01,$D$1:$D$10,-1),3)

​

 

[AlanY]

your 20% for over 20m2 is a bit odd.
you can change that to what ever it should be, e.g. for 120%

TT.xlsx
	A	B	C	D	E	F
1	8.11	17.42		99	15	120%
2	11.99	24.948		40	14	120%
3	11.54	24.408		35	13	120%
4	12.66	25.752		30	12	120%
5	25.5	45		25	11	120%
6	33.75	56.1		20	10	120%
7	45	72		15	8.8	120%
8				10	0.6	200%
9				8	0.5	220%
10				5	0.4	240%
Sheet4

Cell Formulas
Range	Formula
B1:B7	B1	=(A1+INDEX($D$1:$F$10,MATCH(A1*1.01,$D$1:$D$10,-1),2))*INDEX($D$1:$F$10,MATCH(A1*1.01,$D$1:$D$10,-1),3)