text formula + formatting

[jw01]

hello all

having some issue with text formula

Part A:

TEXT(M26-G26,"$#,#")

in the above, i want to show the "$" by two decimals i.e. $2.06; right now it shows $2
-in addition, i want it to go "red" with brackets if it is less than 0 i.e. red --> ($6.21).

Part B:

TEXT(((M26-G26)/G26),"##.##%")

I want the above percentage to show in red and in brackets when below 0.00%

Thank you all!

[judgeh59]

try something like this

Please Login or Register  to view this content.

similiar for the %

[jw01]

try something like this

Please Login or Register  to view this content.

similiar for the %

hey

I tried your approach:

=TEXT(M37-G37,"$#,##0.00_);[Red]($#,##0.00)")

the items appear in bracket but not in "Red"....if i remove the quotation marks, the formula doesnt work?

thanks.

this is my entire formula

Please Login or Register  to view this content.

[Sam Capricci]

for part A, can you use ="$"&M26-G26?

[Pete_UK]

Try this for Part A:

=IF(G26>M26,"(","")&TEXT(ABS(M26-G26),"$#,##0.00")&IF(G26>M26,")","")

and this for Part B:

=IF(G26>M26,"(","")&TEXT(ABS((M26-G26)/G26),"#0.00%")&IF(G26>M26,")","")

The TEXT function does not support colours in the format string, so you will need to apply conditional formatting to get red if M26-G26 is less than zero.

Hope this helps.

Pete

[jw01]

Try this for Part A:

=IF(G26>M26,"(","")&TEXT(ABS(M26-G26),"$#,##0.00")&IF(G26>M26,")","")

and this for Part B:

=IF(G26>M26,"(","")&TEXT(ABS((M26-G26)/G26),"#0.00%")&IF(G26>M26,")","")

The TEXT function does not support colours in the format string, so you will need to apply conditional formatting to get red if M26-G26 is less than zero.

Hope this helps.

Pete

hey Pete

How do i merge both Part A and Part B based on your formula?

my primary formula is derived off "choose" formula i.e. see below:
=CHOOSE($S$21,TEXT(M26-G26,"#,##_);[Red](#,##)"),TEXT(((M26-G26)/G26),"#,##0.00%_);[Red](#,##0.00%)"))

Thanks again!

[Pete_UK]

Try this:

=IF($S$21=1,IF(G26>M26,"(","")&TEXT(ABS(M26-G26),"$#,##0.00")&IF(G26>M26,")",""),IF(G26>M26,"(","")&TEXT(ABS((M26-G26)/G26),"#0.00%")&IF(G26>M26,")",""))

Don't forget the CF to turn it red.

Hope this helps.

Pete

[jw01]

Try this:

=IF($S$21=1,IF(G26>M26,"(","")&TEXT(ABS(M26-G26),"$#,##0.00")&IF(G26>M26,")",""),IF(G26>M26,"(","")&TEXT(ABS((M26-G26)/G26),"#0.00%")&IF(G26>M26,")",""))

Don't forget the CF to turn it red.

Hope this helps.

Pete

hey Pete

using your formula...i still cannot color condition to show red - the same issue as the choose formula i had.

i highlighted the range and CF for less than 0 and it shows nothing. it is not recognizing the # format i assume...any way to work around that?

thx u

[popipipo]

Maybe this??

[Pete_UK]

Borrowing popipipo's workbook, I've put the formula from Post #7 in cell J26, which is now formatted as General. I've applied conditional formatting to that cell to turn it red if negative. You can use cell S21 to change the units - set to 1 the units will be $, but any other value in S21 (or blank) will return %.

Hope this helps.

Pete