acknowledge divisability of $20 indicate mathematical results

[Guest]

Need help designing a formula to discern the nearest increment of $20.00 then
show the results - one to indicate the result of the original subtraction
(e.g., $539.00 - $520.00 with $520.00 being the result) and the other column
with the remaining amount (e.g., $19.00).

This example would include three columns: 1) The original amount, 2) the
total result of $20.00 increments, and 3) what remains from the original
amount.

Is this possible? Thanx for any assistance.

 

[Guest]

Here are some options to use in Cell B1 if you are testing a value in Cell A1:

If you want the next HIGHER increment of $20 use:
=Ceiling(A1,20)

If you want the closest LOWER multiple of $20 use:
=QUOTIENT(A1,20)*20

If you want the NEAREST multiple of $20 use:
=ROUND(A1/20,0)*20

For any of them, use this formula in Cell C1:
=A1-B1

Does that help?

Regards,
Ron

 

[Tushar Mehta]

The problem is to find the largest integer n such that n*20<=T, where T
stands for the target value.

Now, you can solve this algebraically. Since n*20<=T, n=Int(T/20).
This works only for T>=0.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005