Formula to calculate price per +0,5kg

[IwanGerbes]

Hi,
Guess it is an easy one, and sorry if it is already here, but couldn´t find it, as i don´t exactly now how to formulate it.
I´m searching for a formula to calculate in the following way:
[TABLE="width: 320"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Upto 500 gms[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 152"]
<tbody>[TR]
[TD="class: xl65, width: 152"]Every additional 500 gms[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Within-city[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85, align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Regional (<500Kms)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]36[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Metro-Metro[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]38[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Rest of country[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]42[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]North East, South East[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]55[/TD]
[TD]44[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So if a shipment is 4,8 kg in Regional, it is 36 + 9x 30.
Is there a formula, which could do this? Or should i make an hidden sheet and do vlookup based on the weight to find the matching number?

 

[JustynaMK]

Hi Iwan,

Assuming that your table is pasted into cells A1:C6 (where column A is "Geography", column B is "upto 500 gms" and column C is "Every additional 500 gms"), you could do the following:

In cell E9, Specify the region that you are currently analyzing:
Regional (<500Kms)

Write the amount in cell F8:
4.8

Paste the following formula into cell F9:
=INDEX(B1:B6,MATCH(E9,A1:A6,0))+IF(F8>0.5,ROUNDUP((F8-0.5)/0.5,0)*INDEX(C1:C6,MATCH(E9,A1:A6,0)),0)

The final result should be 306.

Just to break it down:
INDEX(B1:B6,MATCH(E9,A1:A6,0))
returns the value for the first 500 gms (36)
ROUNDUP((F8-0.5)/0.5,0)
returns the value of 9 (4.8 - 0.5 divided by 0.5 is 8.6, and we are rounding it up to 9)
INDEX(C1:C6,MATCH(E9,A1:A6,0))
returns the unit value for each additional 500gms (30)
We also want to make sure that for the first 500gms we only get the value of 36, thus the IF statement in the second part of the formula:
IF(F8>0.5,...,0)

Let me know if you have any comments. I hope this is a good starting point.