| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use Ceiling to round?
Can anyone help me with a rounding issue
I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
You can use MRound from the analysis toolpack which rounds to a multiple but
if you send it to someone who does not have the toolpack installed the it turns into a #value Error. That being the case I prefer... =round((F7*F27)/500, 0) * 500 -- HTH... Jim Thomlinson "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
Sorry forgot to subtact 5...
=round((F7*F27)/500, 0) * 500 - 5 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: You can use MRound from the analysis toolpack which rounds to a multiple but if you send it to someone who does not have the toolpack installed the it turns into a #value Error. That being the case I prefer... =round((F7*F27)/500, 0) * 500 -- HTH... Jim Thomlinson "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
what's strange is that all of these work in some instances but not all
What about 16,995*125.6325 = 2,135,124 Round to the nearest and minus 5 = 2,092,995 but it's giving me 2,134,995 "Jim Thomlinson" wrote: Sorry forgot to subtact 5... =round((F7*F27)/500, 0) * 500 - 5 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: You can use MRound from the analysis toolpack which rounds to a multiple but if you send it to someone who does not have the toolpack installed the it turns into a #value Error. That being the case I prefer... =round((F7*F27)/500, 0) * 500 -- HTH... Jim Thomlinson "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
2,135,124 rounded to the nearest 500 is
2,135,000 subtract 5 and you get 2,134,995 Where are you getting 2,092,995 -- HTH... Jim Thomlinson "Heather" wrote: what's strange is that all of these work in some instances but not all What about 16,995*125.6325 = 2,135,124 Round to the nearest and minus 5 = 2,092,995 but it's giving me 2,134,995 "Jim Thomlinson" wrote: Sorry forgot to subtact 5... =round((F7*F27)/500, 0) * 500 - 5 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: You can use MRound from the analysis toolpack which rounds to a multiple but if you send it to someone who does not have the toolpack installed the it turns into a #value Error. That being the case I prefer... =round((F7*F27)/500, 0) * 500 -- HTH... Jim Thomlinson "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
you have a good point .. in another workbook we have the person has used ceiling in their formula but it's looking at something a little different and our numbers are supposed to be able to tie out? theirs says: = Ceiling(E8*G86,500)-5 1255697*(1+(2/3),500)-5 = 2,092,995 Jim Thomlinson" wrote: 2,135,124 rounded to the nearest 500 is 2,135,000 subtract 5 and you get 2,134,995 Where are you getting 2,092,995 -- HTH... Jim Thomlinson "Heather" wrote: what's strange is that all of these work in some instances but not all What about 16,995*125.6325 = 2,135,124 Round to the nearest and minus 5 = 2,092,995 but it's giving me 2,134,995 "Jim Thomlinson" wrote: Sorry forgot to subtact 5... =round((F7*F27)/500, 0) * 500 - 5 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: You can use MRound from the analysis toolpack which rounds to a multiple but if you send it to someone who does not have the toolpack installed the it turns into a #value Error. That being the case I prefer... =round((F7*F27)/500, 0) * 500 -- HTH... Jim Thomlinson "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
Try
=FLOOR(F7*F27,500)-5 If this post helps click Yes --------------- Jacob Skaria "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
Thank you everyone!!! :) This was a huge help!
"Jacob Skaria" wrote: Try =FLOOR(F7*F27,500)-5 If this post helps click Yes --------------- Jacob Skaria "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
Make sure the Analysis Tool-Pak Add-in is activated. (Tools - Add-ins).
Formula will be something like: =MROUND(A2,500)-5 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
the formula worked for 1 instance but not others and I'm not sure why??? I
tried this one and the Round and the Floor and I can't seem to get any of them to work for this instance: F7*F28 = 2,135,124 Round to the nearest 500 and subtract 5 = 2,092,995 but MRound(F7*F28,500)-5 = 2,134,995 "Luke M" wrote: Make sure the Analysis Tool-Pak Add-in is activated. (Tools - Add-ins). Formula will be something like: =MROUND(A2,500)-5 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to use Ceiling to round?
As Jim pointed out, 2,135,124 rounded to the nearest 50 is 2,135,000.
You'll need to show us the complete formula/data that's giving 2,092,995 for us to figure out what the discrepency is. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Heather" wrote: the formula worked for 1 instance but not others and I'm not sure why??? I tried this one and the Round and the Floor and I can't seem to get any of them to work for this instance: F7*F28 = 2,135,124 Round to the nearest 500 and subtract 5 = 2,092,995 but MRound(F7*F28,500)-5 = 2,134,995 "Luke M" wrote: Make sure the Analysis Tool-Pak Add-in is activated. (Tools - Add-ins). Formula will be something like: =MROUND(A2,500)-5 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Heather" wrote: Can anyone help me with a rounding issue I have a formula that looks like this F7*F27 16,995*.92134 = 15,658.17 I then need to Round to the nearest 500 and then subtract 5 15,659 rounded to the neares is 15,500 then subtract 5 = 15,495 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MOD and Ceiling formula used to round up and down to 49 and 99 | Excel Discussion (Misc queries) | |||
| Roundup or Ceiling Function to round to a specific number | Excel Discussion (Misc queries) | |||
| ceiling | Excel Discussion (Misc queries) | |||
| Round/Ceiling on an IF function returning numerical value or text | Excel Worksheet Functions | |||
| Max, Ceiling, If, Etc | Excel Discussion (Misc queries) | |||
Hybrid Mode
