Combining text and results

[sarahrosenberg]

I am trying to display the min and max of a column in one cell. For example, the cell would read "From (MIN) to (MAX)" Is this possible?

 

[sarahrosenberg]

I am trying to add into a spreadsheet the following:

"The carrying cost is $1,000.00 per day"

where $1,000.00 pulls from another cell. I have used the following:

="Carrying cost = "&(E34)&" per day"

But it does not give me a dollar format. How do I add in the format? I asked this before on a date format (see below) but I cannot make this work with a currency format.

On 2002-10-15 14:49, Mark W. wrote:
="From "&TEXT(MIN(A1:A20),"m/d/yy")&" to "&TEXT(MAX(A1:A20),"m/d/yy")

 

[IML]

Using the text route, it would be
="The cost is "&TEXT(A1,"$0.00")&" per day"
You can use just about any format this route.

Excel has a built in function to address your particular case. You could use
="The cost is "&DOLLAR(A1,2)&" per day"
where 2 indicate the cents places you want shown.

On 2002-12-11 12:27, sarahrosenberg wrote:
I am trying to add into a spreadsheet the following:

"The carrying cost is $1,000.00 per day"

where $1,000.00 pulls from another cell. I have used the following:

="Carrying cost = "&(E34)&" per day"

But it does not give me a dollar format. How do I add in the format? I asked this before on a date format (see below) but I cannot make this work with a currency format.

On 2002-10-15 14:49, Mark W. wrote:
="From "&TEXT(MIN(A1:A20),"m/d/yy")&" to "&TEXT(MAX(A1:A20),"m/d/yy")

 

[Mark W.]

Or...

="The cost is "&TEXT(A1,"$#,##0.00")&" per day"

 

[sarahrosenberg]

I am trying to duplicate this formula, but for a number with zero decimal places. It is not working. Do you see something out of line?

="Today's Goal: "&Number(Sheet2!I38,"0")&" Retail and "&Sheet2!G39&" Fleet!"

Should read Today's Goal: 658 Retail and 100 Fleet!

Thanks!

 

[Aladin Akyurek]

I am trying to duplicate this formula, but for a number with zero decimal places. It is not working. Do you see something out of line?

="Today's Goal: "&Number(Sheet2!I38,"0")&" Retail and "&Sheet2!G39&" Fleet!"

Should read Today's Goal: 658 Retail and 100 Fleet!

Thanks!

Are you trying to drop the decimal part of a number in the result string, e.g., 123.67 ==> 123?

BTW, Number is no a worksheet function.

 

[sarahrosenberg]

Ideally, I would like it to round. But dropping the decimal would be ok. Right now, it is showing up with a very long decimal:

Today's Goal: 647.555555555556 Retail and 100 Fleet!

 

[IML]

You could use something along the lines of
="todays goal "&TEXT(ROUND(Sheet2!I38,0),"0")&" retail and "&TEXT(ROUND(Sheet2!G39,0),"0")&" fleet!"

or on second thought if you just round it
="todays goal "&ROUND(Sheet2!I38,0)&" retail and "&ROUND(Sheet2!G39,0)&" fleet!"

I think all that will show up will be the integer.

 

[sarahrosenberg]

Thank you! :D