If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula calculating to the 3rd decimal
I am using simple addition & multiplication formulas and the sum appears to
be calculating to the 3rd decimal which is making my totals unacceptable. My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? |
#2
|
|||
|
|||
Formula calculating to the 3rd decimal
Hi,
You can use the following array formula (Ctrl+Shift+Enter). SUM(ROUND(A1:A2,2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Shayla2008" wrote in message ... I am using simple addition & multiplication formulas and the sum appears to be calculating to the 3rd decimal which is making my totals unacceptable. My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? |
#3
|
|||
|
|||
Formula calculating to the 3rd decimal
The sum is still rounding up from the 3rd decimal. Also, it seems to be
doing it only when the cell reference is a formula. Example: cell A3 =sum(A1-A2) is $2510.63 cell A4 =sum (A3*.05) is $125.53 cell A5 =sum(A3*.07) is $ 175.74 =sum(A3+A4+A5) should equal 2811.90 but excel is calculating it at 2811.91 The larger the numbers I deal with the larger the discrepancy becomes. I have never encountered this in excel 2003... Thanks, Shay "Ashish Mathur" wrote: Hi, You can use the following array formula (Ctrl+Shift+Enter). SUM(ROUND(A1:A2,2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Shayla2008" wrote in message ... I am using simple addition & multiplication formulas and the sum appears to be calculating to the 3rd decimal which is making my totals unacceptable. My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? |
#4
|
|||
|
|||
Formula calculating to the 3rd decimal
Hi,
First, you didn't tell us what was in A1 and A2 but even so your math is incorrect sum (A3*.05) is $125.53 is not correct, instead it is 125.5315 =sum(A3*.07) is $ 175.74 is not correct either it is 175.7441 The sum of these first 3 numbers is 2811.9056 which rounds up to 2811.91 ================= Second, the formulas you are using should not be written as they are, although it has no effect on the results, they should be =A3*0.05 =A3*0.07 =SUM(A3:A5) =============== Third, if you really want to ignore the values beyond the second decimal place then =TRUNC(A3*0.05,2) =TRUNC(A3*0.07,2) =SUM(A3:A5) If you only want the final number treated this way then: =SUMPRODUCT(TRUNC(A3:A5,2)) If this helps, please click the Yes button Cheers, Shane Devenshire "Shayla2008" wrote: I am using simple addition & multiplication formulas and the sum appears to be calculating to the 3rd decimal which is making my totals unacceptable. My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? |
#5
|
|||
|
|||
Formula calculating to the 3rd decimal
On Nov 26, 8:41*pm, Shayla2008
wrote: I have never encountered this in excel 2003 I am surprised, because this is actually a very common problem. Previous, you wrote: My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? Formatting only changes the __appearance__ of numbers. The cell value is often different. For example, enter the number 12.345, then format as Number with 2 decimal places. The value __appears__ to be 12.35, but it is still 12.345, a fact that you can confirm by writing =(A1 = 12.35) into a cell; the result will be FALSE. To answer your question, there are two common ways to do this: 1. Set the option Tools Option Calculation Precision as Displayed. 2. Use the ROUND function liberally. (See below.) I usually deprecate #1 because: (a) you can unintentionally lose precision permanently; and (b) you might still not get the "correct" result (i.e. to your satisfaction) when combining cells with different precision. Example: cell A3 *=sum(A1-A2) is $2510.63 cell A4 *=sum (A3*.05) is $125.53 cell A5 *=sum(A3*.07) is $ 175.74 =sum(A3+A4+A5) should equal 2811.90 First, applying suggestion #2, I would write those formulas as A3: =round(A1-A2, 2) A4: =round(A3*0.05, 2) A5: =round(A3*0.07, 2) A6: =round(A3+A4+A5, 2) Second, unless A1 and A2 contain constants, I would also round the formulas in those cells. Rounding the sum in A6 ensures that WYSIWYG. However, if your intention is to retain the full precision of the values calculated in A1:A5 and you only want to ensure that the sum in A6 uses their rounded values, the second or both of the following formulas might meet you needs: A3: =round(A1,2) - round(A2,2) A6: =sum(round(A3:A5,2)) Note that the second formula is an array formula. Use ctrl-shift- Enter to commit instead of simply Enter. |
#6
|
|||
|
|||
Formula calculating to the 3rd decimal
Thank you,
the TRUNC function has fixed my problem. "Shane Devenshire" wrote: Hi, First, you didn't tell us what was in A1 and A2 but even so your math is incorrect sum (A3*.05) is $125.53 is not correct, instead it is 125.5315 =sum(A3*.07) is $ 175.74 is not correct either it is 175.7441 The sum of these first 3 numbers is 2811.9056 which rounds up to 2811.91 ================= Second, the formulas you are using should not be written as they are, although it has no effect on the results, they should be =A3*0.05 =A3*0.07 =SUM(A3:A5) =============== Third, if you really want to ignore the values beyond the second decimal place then =TRUNC(A3*0.05,2) =TRUNC(A3*0.07,2) =SUM(A3:A5) If you only want the final number treated this way then: =SUMPRODUCT(TRUNC(A3:A5,2)) If this helps, please click the Yes button Cheers, Shane Devenshire "Shayla2008" wrote: I am using simple addition & multiplication formulas and the sum appears to be calculating to the 3rd decimal which is making my totals unacceptable. My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? |
#7
|
|||
|
|||
Formula calculating to the 3rd decimal
Sorry, this did work also...I didn't understand the array and didn't
ctrl+shift+enter. I have read further on array formulas. Thank you, Shay "Ashish Mathur" wrote: Hi, You can use the following array formula (Ctrl+Shift+Enter). SUM(ROUND(A1:A2,2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Shayla2008" wrote in message ... I am using simple addition & multiplication formulas and the sum appears to be calculating to the 3rd decimal which is making my totals unacceptable. My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? |
#8
|
|||
|
|||
Formula calculating to the 3rd decimal
On Nov 27, 6:59*pm, Shayla2008
wrote: the TRUNC function has fixed my problem. The TRUNC function does not give you the same result as (you wrote) "formatting in the cells are for 2 decimal places". Excel rounds, not truncates. Use the ROUND function if you want to (you wrote) "ensure the formula is also calculating to only the 2nd decimal place". |
#9
|
|||
|
|||
Need help on Roundup
I Badly need an help!
For example : Am getting a value as 254.849 and 154.158, From the above two value , first value 3rd decimal digit is 9 so i need an round up for this to 254.85 and if the 3rd decimal digit is not equal to 9 then its should display the 154.158 as it is. Is there any possiblity? please mail me Shayla200 wrote: Sorry, this did work also... 27-Nov-08 Sorry, this did work also...I did not understand the array and did not ctrl+shift+enter. I have read further on array formulas. Thank you, Shay "Ashish Mathur" wrote: Previous Posts In This Thread: On Wednesday, November 26, 2008 9:15 PM Shayla200 wrote: Formula calculating to the 3rd decimal I am using simple addition & multiplication formulas and the sum appears to be calculating to the 3rd decimal which is making my totals unacceptable. My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? On Wednesday, November 26, 2008 9:40 PM Ashish Mathur wrote: Hi,You can use the following array formula (Ctrl+Shift+Enter). Hi, You can use the following array formula (Ctrl+Shift+Enter). SUM(ROUND(A1:A2,2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com On Wednesday, November 26, 2008 11:41 PM Shayla200 wrote: The sum is still rounding up from the 3rd decimal. The sum is still rounding up from the 3rd decimal. Also, it seems to be doing it only when the cell reference is a formula. Example: cell A3 =sum(A1-A2) is $2510.63 cell A4 =sum (A3*.05) is $125.53 cell A5 =sum(A3*.07) is $ 175.74 =sum(A3+A4+A5) should equal 2811.90 but excel is calculating it at 2811.91 The larger the numbers I deal with the larger the discrepancy becomes. I have never encountered this in excel 2003... Thanks, Shay "Ashish Mathur" wrote: On Thursday, November 27, 2008 1:33 AM ShaneDevenshir wrote: Hi,First, you didn't tell us what was in A1 and A2 but even so your math is Hi, First, you didn't tell us what was in A1 and A2 but even so your math is incorrect sum (A3*.05) is $125.53 is not correct, instead it is 125.5315 =sum(A3*.07) is $ 175.74 is not correct either it is 175.7441 The sum of these first 3 numbers is 2811.9056 which rounds up to 2811.91 ================= Second, the formulas you are using should not be written as they are, although it has no effect on the results, they should be =A3*0.05 =A3*0.07 =SUM(A3:A5) =============== Third, if you really want to ignore the values beyond the second decimal place then =TRUNC(A3*0.05,2) =TRUNC(A3*0.07,2) =SUM(A3:A5) If you only want the final number treated this way then: =SUMPRODUCT(TRUNC(A3:A5,2)) If this helps, please click the Yes button Cheers, Shane Devenshire "Shayla2008" wrote: On Thursday, November 27, 2008 9:00 PM joeu2004 wrote: Formula calculating to the 3rd decimal On Nov 26, 8:41=A0pm, Shayla2008 wrote: I am surprised, because this is actually a very common problem. Previous, you wrote: Formatting only changes the __appearance__ of numbers. The cell value is often different. For example, enter the number 12.345, then format as Number with 2 decimal places. The value __appears__ to be 12.35, but it is still 12.345, a fact that you can confirm by writing =3D(A1 =3D 12.35) into a cell; the result will be FALSE. To answer your question, there are two common ways to do this: 1. Set the option Tools Option Calculation Precision as Displayed. 2. Use the ROUND function liberally. (See below.) I usually deprecate #1 because: (a) you can unintentionally lose precision permanently; and (b) you might still not get the "correct" result (i.e. to your satisfaction) when combining cells with different precision. First, applying suggestion #2, I would write those formulas as A3: =3Dround(A1-A2, 2) A4: =3Dround(A3*0.05, 2) A5: =3Dround(A3*0.07, 2) A6: =3Dround(A3+A4+A5, 2) Second, unless A1 and A2 contain constants, I would also round the formulas in those cells. Rounding the sum in A6 ensures that WYSIWYG. However, if your intention is to retain the full precision of the values calculated in A1:A5 and you only want to ensure that the sum in A6 uses their rounded values, the second or both of the following formulas might meet you needs: A3: =3Dround(A1,2) - round(A2,2) A6: =3Dsum(round(A3:A5,2)) Note that the second formula is an array formula. Use ctrl-shift- Enter to commit instead of simply Enter. On Thursday, November 27, 2008 9:59 PM Shayla200 wrote: Formula calculating to the 3rd decimal Thank you, the TRUNC function has fixed my problem. "Shane Devenshire" wrote: On Thursday, November 27, 2008 10:37 PM Shayla200 wrote: Sorry, this did work also... Sorry, this did work also...I did not understand the array and did not ctrl+shift+enter. I have read further on array formulas. Thank you, Shay "Ashish Mathur" wrote: On Sunday, November 30, 2008 6:31 PM joeu2004 wrote: Formula calculating to the 3rd decimal On Nov 27, 6:59=A0pm, Shayla2008 wrote: The TRUNC function does not give you the same result as (you wrote) "formatting in the cells are for 2 decimal places". Excel rounds, not truncates. Use the ROUND function if you want to (you wrote) "ensure the formula is also calculating to only the 2nd decimal place". Submitted via EggHeadCafe - Software Developer Portal of Choice Adding WCF Service References http://www.eggheadcafe.com/tutorials...ce-refere.aspx |
#10
|
|||
|
|||
Need help on Roundup
How about this
=H1+(RIGHT(H1,1)="9")*0.001 -- HTH Bob ganesh c wrote in message ... I Badly need an help! For example : Am getting a value as 254.849 and 154.158, From the above two value , first value 3rd decimal digit is 9 so i need an round up for this to 254.85 and if the 3rd decimal digit is not equal to 9 then its should display the 154.158 as it is. Is there any possiblity? please mail me Shayla200 wrote: Sorry, this did work also... 27-Nov-08 Sorry, this did work also...I did not understand the array and did not ctrl+shift+enter. I have read further on array formulas. Thank you, Shay "Ashish Mathur" wrote: Previous Posts In This Thread: On Wednesday, November 26, 2008 9:15 PM Shayla200 wrote: Formula calculating to the 3rd decimal I am using simple addition & multiplication formulas and the sum appears to be calculating to the 3rd decimal which is making my totals unacceptable. My formatting in the cells are for 2 decimal places. How can I ensure the formula is also calculating to only the 2nd decimal place? On Wednesday, November 26, 2008 9:40 PM Ashish Mathur wrote: Hi,You can use the following array formula (Ctrl+Shift+Enter). Hi, You can use the following array formula (Ctrl+Shift+Enter). SUM(ROUND(A1:A2,2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com On Wednesday, November 26, 2008 11:41 PM Shayla200 wrote: The sum is still rounding up from the 3rd decimal. The sum is still rounding up from the 3rd decimal. Also, it seems to be doing it only when the cell reference is a formula. Example: cell A3 =sum(A1-A2) is $2510.63 cell A4 =sum (A3*.05) is $125.53 cell A5 =sum(A3*.07) is $ 175.74 =sum(A3+A4+A5) should equal 2811.90 but excel is calculating it at 2811.91 The larger the numbers I deal with the larger the discrepancy becomes. I have never encountered this in excel 2003... Thanks, Shay "Ashish Mathur" wrote: On Thursday, November 27, 2008 1:33 AM ShaneDevenshir wrote: Hi,First, you didn't tell us what was in A1 and A2 but even so your math is Hi, First, you didn't tell us what was in A1 and A2 but even so your math is incorrect sum (A3*.05) is $125.53 is not correct, instead it is 125.5315 =sum(A3*.07) is $ 175.74 is not correct either it is 175.7441 The sum of these first 3 numbers is 2811.9056 which rounds up to 2811.91 ================= Second, the formulas you are using should not be written as they are, although it has no effect on the results, they should be =A3*0.05 =A3*0.07 =SUM(A3:A5) =============== Third, if you really want to ignore the values beyond the second decimal place then =TRUNC(A3*0.05,2) =TRUNC(A3*0.07,2) =SUM(A3:A5) If you only want the final number treated this way then: =SUMPRODUCT(TRUNC(A3:A5,2)) If this helps, please click the Yes button Cheers, Shane Devenshire "Shayla2008" wrote: On Thursday, November 27, 2008 9:00 PM joeu2004 wrote: Formula calculating to the 3rd decimal On Nov 26, 8:41=A0pm, Shayla2008 wrote: I am surprised, because this is actually a very common problem. Previous, you wrote: Formatting only changes the __appearance__ of numbers. The cell value is often different. For example, enter the number 12.345, then format as Number with 2 decimal places. The value __appears__ to be 12.35, but it is still 12.345, a fact that you can confirm by writing =3D(A1 =3D 12.35) into a cell; the result will be FALSE. To answer your question, there are two common ways to do this: 1. Set the option Tools Option Calculation Precision as Displayed. 2. Use the ROUND function liberally. (See below.) I usually deprecate #1 because: (a) you can unintentionally lose precision permanently; and (b) you might still not get the "correct" result (i.e. to your satisfaction) when combining cells with different precision. First, applying suggestion #2, I would write those formulas as A3: =3Dround(A1-A2, 2) A4: =3Dround(A3*0.05, 2) A5: =3Dround(A3*0.07, 2) A6: =3Dround(A3+A4+A5, 2) Second, unless A1 and A2 contain constants, I would also round the formulas in those cells. Rounding the sum in A6 ensures that WYSIWYG. However, if your intention is to retain the full precision of the values calculated in A1:A5 and you only want to ensure that the sum in A6 uses their rounded values, the second or both of the following formulas might meet you needs: A3: =3Dround(A1,2) - round(A2,2) A6: =3Dsum(round(A3:A5,2)) Note that the second formula is an array formula. Use ctrl-shift- Enter to commit instead of simply Enter. On Thursday, November 27, 2008 9:59 PM Shayla200 wrote: Formula calculating to the 3rd decimal Thank you, the TRUNC function has fixed my problem. "Shane Devenshire" wrote: On Thursday, November 27, 2008 10:37 PM Shayla200 wrote: Sorry, this did work also... Sorry, this did work also...I did not understand the array and did not ctrl+shift+enter. I have read further on array formulas. Thank you, Shay "Ashish Mathur" wrote: On Sunday, November 30, 2008 6:31 PM joeu2004 wrote: Formula calculating to the 3rd decimal On Nov 27, 6:59=A0pm, Shayla2008 wrote: The TRUNC function does not give you the same result as (you wrote) "formatting in the cells are for 2 decimal places". Excel rounds, not truncates. Use the ROUND function if you want to (you wrote) "ensure the formula is also calculating to only the 2nd decimal place". Submitted via EggHeadCafe - Software Developer Portal of Choice Adding WCF Service References http://www.eggheadcafe.com/tutorials...ce-refere.aspx |
Thread Tools | |
Display Modes | |
|
|