Format numbers returned in the function CONCATENATE

K

Kip

I would like to format the numbers that are returned in the function below as
[Blue]$#,##0.00_);[Red]($#,##0.00)
so that the result shows:

147,852.36 needed to break the weekly record of 654,987.32.

NOTE: Positive numbers are blue and negative numbers are red, with the comma
as the thousand separator, two decimal places, and in parenthesis.

FUNCTION IN CELL
=CONCATENATE(SUM(K20-D12)," needed to break the weekly record of ",K20)

I tried this:

=CONCATENATE(TEXT(J26,"[Blue]$#,##0.00_);[Red]($#,##0.00)")&"the quick brown
fox jumps over the dog. "&(SUM(K20-D12)))

But it did not work.

Is it possible?
 
K

Kip

Thanks for the reply Bob.

Can I format the numbers without color?

Bob Umlas said:
you can't format numbers inside a formula with colors based on their values.

Kip said:
I would like to format the numbers that are returned in the function below as
[Blue]$#,##0.00_);[Red]($#,##0.00)
so that the result shows:

147,852.36 needed to break the weekly record of 654,987.32.

NOTE: Positive numbers are blue and negative numbers are red, with the comma
as the thousand separator, two decimal places, and in parenthesis.

FUNCTION IN CELL
=CONCATENATE(SUM(K20-D12)," needed to break the weekly record of ",K20)

I tried this:

=CONCATENATE(TEXT(J26,"[Blue]$#,##0.00_);[Red]($#,##0.00)")&"the quick brown
fox jumps over the dog. "&(SUM(K20-D12)))

But it did not work.

Is it possible?
Click to expand...
Click to expand...
 
K

Kip

Figured it out. The function below returns:

$149,550.15 is needed to break the weekly record of $878,784.15

=CONCATENATE(TEXT(SUM(K20-D12),"$#,##0.00_);($#,##0.00)")&"is needed to
break the weekly record of "&(TEXT(SUM(K20),"$#,##0.00_);( $#,##0.00)")))
====================
:

you can't format numbers inside a formula with colors based on their values.
====================
I would like to format the numbers that are returned in the function below as
[Blue]$#,##0.00_);[Red]($#,##0.00)
so that the result shows:

147,852.36 needed to break the weekly record of 654,987.32.

NOTE: Positive numbers are blue and negative numbers are red, with the comma
as the thousand separator, two decimal places, and in parenthesis.

FUNCTION IN CELL
=CONCATENATE(SUM(K20-D12)," needed to break the weekly record of ",K20)

I tried this:

=CONCATENATE(TEXT(J26,"[Blue]$#,##0.00_);[Red]($#,##0.00)")&"the quick brown
fox jumps over the dog. "&(SUM(K20-D12)))

But it did not work.

Is it possible?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use TODAY() function as the criteria in a SUMIF formula 1
Concatenate in a Cell (&) and working with the numbers. 4
How specify number format of cell value in concatenate function? 1
Text format in "Concatenate" function 1
CountA or DcountA, that is the question?? 1
Sum function when the values in an xls are color coded 2
How do I get the concatenate function in Excel to display results? 4
Can you set to default the # of decimal places in Format Cells? 4

Top