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?

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?

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?

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?

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.

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?

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?

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".

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

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