Formatting Negative Number within Concatenation not Working

**DDmUSA** · 03-20-2012, 06:01 PM

I have a Microsoft Excel 2010 Worksheet with an ESTIMATED COST and an ACTUAL cost column.

I would like to have a third column that shows the numeric difference between actual and estimated, and I would like this number to appear as currency with two decimal places. If this currency amount is negative, I would like it to appear in red within parentheses. Then, with a blank space after this currency value, I would like to see the decimal percentage difference of ACTUAL over the ESTIMATED.

Using the following syntax, I am able to see the two values I want within the same cell, but the currency value is not displaying as RED when it is negative.

=TEXT(B4-D4,"$#,##0.00_);[Red]($#,##0.00)") & " " & TEXT((D4-B4)/B4,"0.00")

Does anyone know why the negative value is not displaying in red for me? I am using Excel 2010 on a Windows 7 machine.

**Marcol** · 03-21-2012, 04:23 AM

If I understand you correctly the formula you are trying to use won't work.
You can't format part of a cell by using a formula, nor can you apply formatting using formulae.

Try this in say cell H2

Please Login or Register  to view this content.

Highlight Column H, then with Conditional Formatting
Formula is:=

Please Login or Register  to view this content.

Format > Font = Red.

See the attached, Columns E:F are only to show other alternatives, they are not required for your problem.
Is this what you are trying to do?