Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
acknowledge divisability of $20 indicate mathematical results
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
acknowledge divisability of $20 indicate mathematical results
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 In article , says... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mathematical Integration | Excel Worksheet Functions | |||
Mathematical Problem | Excel Worksheet Functions | |||
How do you get the X bar mathematical symbol? | Excel Discussion (Misc queries) | |||
How to auto acknowledge a prompt box, Macro | Excel Programming | |||
How to acknowledge a message box from VB program? | Excel Programming |