Costings Formula

[Adcas2018]

Hi,

New to the forum and hoping someone can help what is hopefully straightforward to those in the know!!

I'm looking at getting a spreadsheet together to provide an overall percentage cost of a product that has variable pricing depending on the amount paid....

eg: upto £30000 charges 0.40%, the next £200,000 the charges reduce to 0.30% and the next £200,000 the charges reduce to 0.25%

So a figure of £100,000 would cost 0.40% for £30000 with the remaining £70000 at 0.30% and produce an overall charge of X%.

Ideally looking at having a field where a £ figure can be entered with a field next to it that returns the charge,

Is there a formula that I can use to do this?

Hope you can help!

Thanks

Matt

 

[dreid1011]

Welcome to the board.

If your expected result for that sample is 133000, then perhaps this:

=(MIN(A2,30000)*1.4)+(MAX(0,A2-30000)*1.3)+(MAX(0,A2-230000)*1.25)

If you just want to see the additional charge of 33000, then this:

=(MIN(A2,30000)*0.4)+(MAX(0,A2-30000)*0.3)+(MAX(0,A2-230000)*0.25)