Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding to the nearest cent...currency values
I am having trouble getting either a product or sum to round to the nearest
cent. How do I do this? Example 6015.54x1.4=8421.76, However when I do this in excel, it ends up being 8421.75 for some reason. Or if I choose to add 6015.54 and 2406.22 (the latter a result of multiplying the former by .4) it still gives me 8421.75...the actual calculation works out to 8421.756, so obviously it is just dropping the last number (or rounding it down to the nearest fifth one hundredth?)...I'd prefer seeing as how I am working with currency that it rounded the final number appropriately. Help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding to the nearest cent...currency values
Read this:
http://www.mcgimpsey.com/excel/pennyoff.html -- Kind regards, Niek Otten Microsoft MVP - Excel "Mr Mark" wrote in message ... I am having trouble getting either a product or sum to round to the nearest cent. How do I do this? Example 6015.54x1.4=8421.76, However when I do this in excel, it ends up being 8421.75 for some reason. Or if I choose to add 6015.54 and 2406.22 (the latter a result of multiplying the former by .4) it still gives me 8421.75...the actual calculation works out to 8421.756, so obviously it is just dropping the last number (or rounding it down to the nearest fifth one hundredth?)...I'd prefer seeing as how I am working with currency that it rounded the final number appropriately. Help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding to the nearest cent...currency values
Unless you have the option "Precision as displayed" selected, XL
calculates based on the stored values in the cells, not the displayed values. If your multiplicand or multiplier are calculated, they're probably not exactly 6015.54 or 1.4. You can expand the number of decimal places to find out. Working with currency should always be done with care to round appropriately at each stage. As long as you're using floating point math, there's no general way to prevent rounding errors at the end of a string of calculations. See http://www.mcgimpsey.com/excel/pennyoff.html for more... In article , Mr Mark wrote: I am having trouble getting either a product or sum to round to the nearest cent. How do I do this? Example 6015.54x1.4=8421.76, However when I do this in excel, it ends up being 8421.75 for some reason. Or if I choose to add 6015.54 and 2406.22 (the latter a result of multiplying the former by .4) it still gives me 8421.75...the actual calculation works out to 8421.756, so obviously it is just dropping the last number (or rounding it down to the nearest fifth one hundredth?)...I'd prefer seeing as how I am working with currency that it rounded the final number appropriately. Help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding to the nearest cent...currency values
Unfortunately it would appear I need the precision as displayed to be an
option for individual cells...thanks for the clarification at any rate! "JE McGimpsey" wrote: Unless you have the option "Precision as displayed" selected, XL calculates based on the stored values in the cells, not the displayed values. If your multiplicand or multiplier are calculated, they're probably not exactly 6015.54 or 1.4. You can expand the number of decimal places to find out. Working with currency should always be done with care to round appropriately at each stage. As long as you're using floating point math, there's no general way to prevent rounding errors at the end of a string of calculations. See http://www.mcgimpsey.com/excel/pennyoff.html for more... In article , Mr Mark wrote: I am having trouble getting either a product or sum to round to the nearest cent. How do I do this? Example 6015.54x1.4=8421.76, However when I do this in excel, it ends up being 8421.75 for some reason. Or if I choose to add 6015.54 and 2406.22 (the latter a result of multiplying the former by .4) it still gives me 8421.75...the actual calculation works out to 8421.756, so obviously it is just dropping the last number (or rounding it down to the nearest fifth one hundredth?)...I'd prefer seeing as how I am working with currency that it rounded the final number appropriately. Help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding to nearest 100 | Excel Worksheet Functions | |||
Rounding to the nearest 5 | Excel Discussion (Misc queries) | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Currency Rounding to nearest 5 cents | Excel Discussion (Misc queries) | |||
rounding to 25 cent incriments | Excel Discussion (Misc queries) |