Microsoft Excel - Sum and result comes with round off and 2 decimal

Asked By John on 09-May-12 08:19 AM
Earn up to 10 extra points for answering this tough question.

I would like to sum these numbers as follows and need result should come with 2.decimal point. I used this command,

= " = TOTAL - " & SUM(A18:A21). Can use any formula use with this formula to round off and with 2 decimal point

284.1666667

284.1666667
114.875

293.625

Pichart Y. replied to John on 09-May-12 11:23 AM
Hi John,

Use this formula = " = TOTAL - " & ROUND(SUM(A18:A21),2)

hope this help.

Pichart Y
wally eye replied to John on 09-May-12 11:24 AM
You can use the Text function to format numbers:

="= TOTAL - " & TEXT(SUM(A3:A6),"#,##0.00")

Assuming your numbers are in A3:A6, this uses the Sum function to sum them, and the Text function then formats the sum as you requested:

= TOTAL - 976.83
Devil Scorpio replied to John on 10-May-12 03:10 AM
Hi,

To select the number of digits displayed after the decimal point:

1. Select the Tools menu
2. Select Options
3. Select the Edit tab
4. Select the Fixed decimal Places checkbox
5. Select the appropriate number of places
6. Click OK
Devil Scorpio replied to John on 10-May-12 03:11 AM
Hi,

To select the number of digits displayed after the decimal point:

1. Select the Tools menu
2. Select Options
3. Select the Edit tab
4. Select the Fixed decimal Places checkbox
5. Select the appropriate number of places
6. Click OK
John replied to wally eye on 10-May-12 05:08 AM
Not comming right total
wally eye replied to John on 10-May-12 11:59 AM
You might check that you have the range set properly, I used A3:A6, yours is probably somewhere else.  I just double-checked the 976.83 against the values you posted, it matches a rounded 976.8333 (or 976.8334 manually)
John replied to wally eye on 12-May-12 07:51 AM
Ok Thank you. Its working. But if I want the result in another sheet then how the command will come? Hoping you can give a suggestion.
John replied to Pichart Y. on 12-May-12 07:54 AM
Thaks Its woking but if I want the result in another sheet then how the command will come.
Pichart Y. replied to John on 12-May-12 08:17 AM
Hi John,

Just cut (ctrl X) the formula, then paste to the sheet you want...Excel will automatically adjust the formula for you.

Try it, and let me know the result.

pichart Y.
John replied to John on 15-May-12 03:41 AM

3 =       


Thank you for your early help. now I want the result in another sheet.
  Following are the command and result.             
=A15/3 = 284.1666667
=A15/3 = 284.1666667
=B15/2 = 114.875
=D15/2 = 299.75

Total =    982.9583333
= " = TOTAL - " & ROUND(SUM(A18:A21),2) = = TOTAL - 982.96

I want this result in another sheet. How it possible?. Can you suggest a way to for this? Thanks in advance.
John replied to Pichart Y. on 15-May-12 03:44 AM

Thank you for your early help. now I want the result in another sheet.


Following are the command and result.
=A15/3 = 284.1666667
=A15/3 = 284.1666667
=B15/2 = 114.875
=D15/2 = 299.75

Total = 982.9583333
= " = TOTAL - " & ROUND(SUM(A18:A21),2) = = TOTAL - 982.96 
 
I want this result in another sheet. How it possible?. Can you suggest a way to for this? Thanks in advance.

John replied to wally eye on 15-May-12 03:46 AM

Thank you for your early help. now I want the result in another sheet.


Following are the command and result.
=A15/3 = 284.1666667
=A15/3 = 284.1666667
=B15/2 = 114.875
=D15/2 = 299.75

Total = 982.9583333
= " = TOTAL - " & ROUND(SUM(A18:A21),2) = = TOTAL - 982.96 
 
I want this result in another sheet. How it possible?. Can you suggest a way to for this? Thanks in advance.
Pichart Y. replied to John on 15-May-12 04:48 AM
Hi John,

Just cut (ctrl X) that cell, then paste it to the sheet you want...Excel will automatically adjust the formula for you.

Try it, and let me know the result.

pichart Y.