Macro or Formula to calculate different price thresholds?

**Akshayhk** · 01-05-2011, 09:47 PM

Hello everybody,

I want to calculate a formula based on the increment of a value in a cell. For example, I want to calculate the weight based on $25 for the first Kg and 15$ for every subsequent Kg. I can write a formula for that, but it takes for only 1 Kg increments and not for decimals like 1.5 Kg or 1.2 Kg...etc. Any help would be appreciated.

Thanks

**Conor** · 01-05-2011, 10:08 PM

Your question is not clear. I do not think you have supplied all the necessary info or that it is even an Excel question. Are the weight increments always in 1/10ths of a KG? If each additional KG is $15, does each additional 1/10th KG cost $1.50?

**MarvinP** · 01-05-2011, 10:14 PM

Formula always before even thinking Macro.

**teylyn** · 01-05-2011, 10:20 PM

Hello, assuming B1 = 25 and B2 = 15 for the prices, weight is entered in B3, then price can be calculated with

=IF(B3>1,(B3-1)*B2+B1,B3*B1)

The same principle can be applied to other thresholds. Can you post an example of what you really want to calculate?

**Akshayhk** · 01-05-2011, 11:08 PM

Hi Connor and Teylyn. This is what I want to achieve;
For the first Kg, it is $25. Each subsequent Kg after that is $15. Say if it is 2 Kgs, it is $25+$15=$40, 3 Kgs it is $25+$15+$15=$55...etc. I have created a formula for that as
IF(A2<=1,25,((A2*15)-15)+25) where A2 is the cell I am entering the weight. Yes, each additional 1/10th Kg will cost $1.50.

**teylyn** · 01-05-2011, 11:50 PM

Does that mean that the price for 0.5 kg is also $25? So, everything up to the first full KG is $25?

If so, your formula is correct.

What is the question?

**Akshayhk** · 01-06-2011, 12:23 AM

Originally Posted by Conor

Your question is not clear. I do not think you have supplied all the necessary info or that it is even an Excel question. Are the weight increments always in 1/10ths of a KG? If each additional KG is $15, does each additional 1/10th KG cost $1.50?

Originally Posted by teylyn

Hello, assuming B1 = 25 and B2 = 15 for the prices, weight is entered in B3, then price can be calculated with

=IF(B3>1,(B3-1)*B2+B1,B3*B1)

The same principle can be applied to other thresholds. Can you post an example of what you really want to calculate?

Originally Posted by MarvinP

Formula always before even thinking Macro.

Originally Posted by teylyn

Does that mean that the price for 0.5 kg is also $25? So, everything up to the first full KG is $25?

If so, your formula is correct.

What is the question?

Yes, everything up to the first full Kg is $25. My question is how can I set the formula for decimal places? That formula works well only for 1 Kg increments. If I enter the value as 1.5 Kg, I get a wrong result.

**teylyn** · 01-06-2011, 07:52 PM

You mean that everything up to the first 1.5 kg is $25?

If so, try

=IF(A2>1.5,25+(A2-1.5)*15,25)

You could also store your increment in a cell, say A1, and then use

=IF(A2>A1,25+(A2-A1)*15,25)

This way, when your increment unit changes, you just need to change A1 and don't have to correct the formula.

cheers