Rounding down

[John]

I was wondering if anyone can tell me if this is possible. I need to create
a formula based on the results of date in two separate fileds. Suppose I
have a field with 31064.47 and another with 28738. I wand to subtract the
2nd number from the first, then round the result to the nearest increment of
500. For example, 31064.47 subtract 28738 equals 2326.47. I would like to
round that result down to the nearest increment of 500 or 2000.

Can this be done, and how?

 

[kassie]

Say you have your 2 numbers in A1 and A2, then where you do the calculation,
enter the following formula:
=ROUNDDOWN(A1-A2,-3)

 

[Bob Phillips]

=FLOOR(A1-A2,500)

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[bplumhoff]

Hello,

If the second number can be greater than the first and if the multiple
of 500 has to be <= the difference:

1. =IF(A1-A2>0,ROUNDDOWN((A1-A2)*2,-3)/2,ROUNDUP((A1-A2)*2/-3)/2)

2. =IF(A1-A2>0,FLOOR(A1-A2,500),CEILING(A1-A2,-500))

HTH,
Bernd