Excel formula problem?

Question

In Excel 2002 (10.2614.2625) running on WinXP SP2 home edition:
Cell A1 is $10.00 and Cell B1 is $10.00. Both cells are formatted to 2 decimals.
Cell C1 contains the following formula: ="total cost $"&SUM(A1:B1)
The result in cell C1 is $20 and not $20.00 even with cell C1 formatted to 2 decimals.
What must I do to get the desired result?

Answer 1

The problem is that cell C1 is text, and therefore not subject to the numeric formatting.
Two choices:
put the formula =SUM(A1:B1) into cell C1, and then
Format Cells Number Custom...
put
"total cost: "$#,##0.00
in the Type box, then click OK

OR

put this formula into C1:
="total cost: "&TEXT(SUM(A1:A2),"$0.00")

Both of these solutions suffer from not dealing correctly with a negative value - but it probably won't matter - and this will give you a step in the right direction...

Answer 2

Make sure all three cells have the same formatting, just to be safe. Hold CTRL and click each cell so you have all three selected. Next, right-click on one of the highlighted cells and choose "Format Cells" in the pop-up menu. Under the Number tab, choose Accounting as your category. Make sure you have two decimal places selected as well as the $ symbol.

Hope that solves it.

Answer 3

It is one of the missunderstanding the format in Excel
Formatting cell will affect on the output of the cell, not hte actual save value.
So The format of one cell will not be in mind when you call the cell value from another cell
So
Use the TEXT function to do this (Formatting inside the formula)

="Price is:"&TEXT( SUM( A1:B1), "#,###.00")

Enjoy my profile, I am the VBAXLMan

Answer 4

=SUM(a1:b1) works for me, with all three cells formatted to currency 2 decimals. You got 'em formatted to currency?