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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Lower bound​

[/td][td]

%​

[/td][td][/td][td]

NPP​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

0,01​

[/td][td]

200%​

[/td][td][/td][td]

7,50​

[/td][td]

22,50​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

10,01​

[/td][td]

190%​

[/td][td][/td][td]

12,75​

[/td][td]

36,98​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

20,01​

[/td][td]

170%​

[/td][td][/td][td]

27,10​

[/td][td]

73,17​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

30,01​

[/td][td]

150%​

[/td][td][/td][td]

34,27​

[/td][td]

85,68​

[/td][/tr]
[/table]

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