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 (e-mail address removed)
Shayla200 wrote:
Sorry, this did work also...
27-Nov-08
Sorry, this did work also...I did not understand the array and did no
ctrl+shift+enter. I have read further on array formulas
Thank you
Sha
:
Previous Posts In This Thread:
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).
Hi
You can use the following array formula (Ctrl+Shift+Enter)
SUM(ROUND(A1:A2,2)
-
Regards
Ashish Mathu
Microsoft Excel MV
www.ashishmathur.com
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.6
cell A4 =sum (A3*.05) is $125.53
cell A5 =sum(A3*.07) is $ 175.7
=sum(A3+A4+A5) should equal 2811.90 but excel is calculating it at 2811.9
The larger the numbers I deal with the larger the discrepancy becomes
I have never encountered this in excel 2003..
Thanks
Sha
:
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
incorrec
sum (A3*.05) is $125.53 is not correct, instead it is
125.531
=sum(A3*.07) is $ 175.74 is not correct either it i
175.744
The sum of these first 3 numbers is 2811.9056 which rounds up to 2811.9
================
Second, the formulas you are using should not be written as they are,
although it has no effect on the results, they should b
=A3*0.0
=A3*0.0
=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 butto
Cheers
Shane Devenshir
:
Re: Formula calculating to the 3rd decimal
On Nov 26, 8:41=A0pm, Shayla2008 <
[email protected]
wrote
I am surprised, because this is actually a very common problem
Previous, you wrote
Formatting only changes the __appearance__ of numbers. The cell valu
is often different. For example, enter the number 12.345, then forma
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 =3
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 a
Displayed
2. Use the ROUND function liberally. (See below.
I usually deprecate #1 because: (a) you can unintentionally los
precision permanently; and (b) you might still not get the "correct
result (i.e. to your satisfaction) when combining cells with differen
precision
First, applying suggestion #2, I would write those formulas a
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 th
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.
RE: Formula calculating to the 3rd decimal
Thank you,
the TRUNC function has fixed my problem.
:
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
:
Re: Formula calculating to the 3rd decimal
On Nov 27, 6:59=A0pm, Shayla2008 <
[email protected]>
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/tutorial...9-dfa51a9fab8e/adding-wcf-service-refere.aspx