Nested IF with intervals

[SimoL]

Hey, I need to make a formula to calculate transport costs:

if weight is above 50 kg and below 100 → 20 €
if weight is between 100 and 200 → 40 €
if weight is between 200 and 300 → 60 €

It goes on like this for every hundred kilogram adding 20 euros. Is there a smart way to do this?

[Pete_UK]

You don't say what it should cost if it is less than 50kg - I've assumed it is "too light" in the following.

You could use this formula in B2, assuming the weight is in A2:

=IFERROR(MATCH(A2,{50,100,200,300,400,500})*20,"too light")

Just add more increments of 100 after the 500 if you need them. Here's an alternative:

=IF(A2<50,"too light",(INT(A2/100)+1)*20)

Hope this helps.

Pete

[SimoL]

Both work great, thanks Pete.

[etaf]

looks like you are rounding up to the nearest 100 and then taking 20% of that number
But to be sure
try
=Roundup(cell with weight, -2 )
that rounds up to nearest 100
then take 20%
=Roundup(cell with weight, -2 ) * 0.2

is that what you are after

if not attach a sample sheet with examples and expected results

does not take < 50 into account, which we can add an IF