Formula for increasing a result to the next highest 1000

[MZ]

A certain tax formula requires increasing the result of a subtraction of two
numbers to the next highest 1000
For example, when subtracting 2250 from 5000, the result of 2750 must be
increased by 250, which is the next highest 1000; this would also be the case
if 2250 is subtracted from 4300, which results in 2050 - you would increase
it to 2100.

If cell A1 is the higher number, Cell A2 is the lower number, and the result
is in Cell A3, what would be the formula in Cell A3 for increasing the result
to the next highest 1000?

The formula needs to be part of an "If" function, since if the subtraction
yields zero, then zero must be in Cell A3.

Thank you

 

[Harald Staff]

Hi MZ

=CEILING(ABS(A1-B1),1000)

assuming that 2050 up to next 1000 is 3000, not 2100.

HTH. Best wishes Harald

 

[Gord Dibben]

You have described two different parameters for rounding.

First one rounds to next highest 1000

2750 to 3000

Second one rounds to next highest 100

2050 to 2100

Which do you want?

ATP's MROUND will do either.

=mround(a1-a2,1000) will look after the first.

=mround(a1-a2,100) will look after the second.

Either one will return 0 if 0


Gord Dibben MS Excel MVP