Progressive Price Calculations - % Based

[davecoppins]

Hi - Hope you can help
I need to add a formula that calculates a margin percentage from a net purchase price (NPP). As the NPP increases, the margin percentage decreases

Example of the outcome required

NPP is between £00.01 & £10.00 calculate NPP + 200% (NPP = £07.50 calculate £07.50 + 200% = £22.50)
NPP is between £10.01 & £00.00 calculate NPP + 190% (NPP = £12.75 calculate £12.75 + 190% = £36.96)
NPP is between £20.01 & £30.00 calculate NPP + 170% (NPP = £27.10 calculate £27.10 + 170% = £73.17)
NPP is between £30.01 & £40.00 calculate NPP + 150% (NPP = £34.27 calculate £34.27 + 150% = £85.68)

Once I have the basic formula layout it should hopefully be easy to maintain and extend as required

Thanks in advance

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this

	A​	B​	C​	D​	E​
1​	Lower bound​	%​		NPP​	Result​
2​	0,01​	200%​		7,50​	22,50​
3​	10,01​	190%​		12,75​	36,98​
4​	20,01​	170%​		27,10​	73,17​
5​	30,01​	150%​		34,27​	85,68​

Create the table in A1:B5

Formula in E2 copied down
=(LOOKUP(D2,A$2:A$5,B$2:B$5)+1)*D2

Hope this helps

M.

 

[davecoppins]

Spot on Marcelo. Many thanks for your help