View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Ron Coderre[_5_] Ron Coderre[_5_] is offline
external usenet poster
 
Posts: 91
Default acknowledge divisability of $20 indicate mathematical results

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