Addition in Excel
Hi,
I work in a bank and I am working on a spreadsheet adds values ONLY the two decimal places and disregard the rest. i.e. +29.901 -report displayed as -- 29.90 +29.902 -report displayed as -- 29.90 +29.903 -report displayed as -- 29.90 +29.904 -report displayed as -- 29.90 +29.905 -report displayed as -- 29.91 ------------ ------------ =149.52 =149.51 (when added from the displayed values.) The problem is that the customers only see up to two decimal places of the amounts. On the example, is someone would run the values based exactly on what is displayed, they would get a different total from excel's total. I realized that limiting the number of decimal places or changes the format (general, accounting, or currency, only changes how the number is displayed but not how excel treats the number in doing operations. Any suggestions/solutions would be greatly appreciated ! |
Addition in Excel
select the cell | format | cells | number tab | category: number |
decimal places : 3 | ok On Oct 7, 12:43*pm, geepeeone wrote: Hi, I work in a bank and I am working on a spreadsheet adds values ONLY the two decimal places and disregard the rest. * i.e. +29.901 *-report displayed as -- 29.90 +29.902 *-report displayed as -- 29.90 +29.903 *-report displayed as -- 29.90 +29.904 *-report displayed as -- 29.90 +29.905 *-report displayed as -- 29.91 ------------ * * * * * * * * * * * * * * *------------ * =149.52 * * * * * * * * * * * * * * * * *=149.51 (when added from the displayed values.) The problem is that the customers only see up to two decimal places of the amounts. *On the example, is someone would run the values based exactly on what is displayed, they would get a different total from excel's total. I realized that limiting the number of decimal places or changes the format (general, accounting, or currency, *only changes how the number is displayed but not how excel treats the number in doing operations. Any suggestions/solutions would be greatly appreciated ! |
Addition in Excel
If you want to round the numbers before you add them, use the ROUND
function. Another option is to use "Precision as displayed" (under Tools/ Options/ Calculation), but I wouldn't recommend it. -- David Biddulph "geepeeone" wrote in message ... Hi, I work in a bank and I am working on a spreadsheet adds values ONLY the two decimal places and disregard the rest. i.e. +29.901 -report displayed as -- 29.90 +29.902 -report displayed as -- 29.90 +29.903 -report displayed as -- 29.90 +29.904 -report displayed as -- 29.90 +29.905 -report displayed as -- 29.91 ------------ ------------ =149.52 =149.51 (when added from the displayed values.) The problem is that the customers only see up to two decimal places of the amounts. On the example, is someone would run the values based exactly on what is displayed, they would get a different total from excel's total. I realized that limiting the number of decimal places or changes the format (general, accounting, or currency, only changes how the number is displayed but not how excel treats the number in doing operations. Any suggestions/solutions would be greatly appreciated ! |
Addition in Excel
Hi David,
The "Precision as Display" was exactly what I was looking for. Thank you soooo much! Gerard "David Biddulph" wrote: If you want to round the numbers before you add them, use the ROUND function. Another option is to use "Precision as displayed" (under Tools/ Options/ Calculation), but I wouldn't recommend it. -- David Biddulph "geepeeone" wrote in message ... Hi, I work in a bank and I am working on a spreadsheet adds values ONLY the two decimal places and disregard the rest. i.e. +29.901 -report displayed as -- 29.90 +29.902 -report displayed as -- 29.90 +29.903 -report displayed as -- 29.90 +29.904 -report displayed as -- 29.90 +29.905 -report displayed as -- 29.91 ------------ ------------ =149.52 =149.51 (when added from the displayed values.) The problem is that the customers only see up to two decimal places of the amounts. On the example, is someone would run the values based exactly on what is displayed, they would get a different total from excel's total. I realized that limiting the number of decimal places or changes the format (general, accounting, or currency, only changes how the number is displayed but not how excel treats the number in doing operations. Any suggestions/solutions would be greatly appreciated ! |
All times are GMT +1. The time now is 09:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com