Cell reference in formula string displays abbreviated value
 

Cell reference in formula string displays abbreviated value, not the
CEILING value with 2 digits after the decimal point as desired.

The formula is: =CONCATENATE("LAB: $",CEILING(M59,0.01)," (",N59,"
MIN. X .40/MIN.)")


In M59 is 1.20

Result displayed is: LAB: $1.2 (3 MIN. X .40/MIN.)

Desired result would be: LAB: $1.20 (3 MIN. X .40/MIN.)

*Odd thing is: if M59 contains a value with the numbers 1-9 (not a 0)
after the 2, then the result appears correctly.

Any ideas how to get the 1.20 to display as 1.20 in the formula
result?

Thank you.

Pete

Cell reference in formula string displays abbreviated value
 

"Pete" wrote:
The formula is: =CONCATENATE("LAB: $",CEILING(M59,0.01),
" (",N59," MIN. X .40/MIN.)")
In M59 is 1.20
Result displayed is: LAB: $1.2 (3 MIN. X .40/MIN.)
Desired result would be: LAB: $1.20 (3 MIN. X .40/MIN.)

At a minimum:

=CONCATENATE("LAB: $",TEXT(CEILING(M59,0.01),"0.00"),
" (",N59," MIN. X .40/MIN.)")

But I think it is preferrable to use ROUNDUP instead of CEILING in this
case.

And there is no need ever to use CONCATENATE. On the contrary, there is
good reason not to, especially in Excel 2003 and earlier.

So I would write:

="LAB: $" & TEXT(ROUNDUP(M59,2),"0.00") & " (" & N59 & " MIN. X .40/MIN.)"

Cell reference in formula string displays abbreviated value
 

joeu2004:

Most grateful for your insightful approach. Thanks so much.

Pete

At a minimum:

=CONCATENATE("LAB: $",TEXT(CEILING(M59,0.01),"0.00"),
" (",N59," MIN. X .40/MIN.)")

But I think it is preferrable to use ROUNDUP instead of CEILING in this
case.

And there is no need ever to use CONCATENATE. *On the contrary, there is
good reason not to, especially in Excel 2003 and earlier.

So I would write:

="LAB: $" & TEXT(ROUNDUP(M59,2),"0.00") & " (" & N59 & " MIN. X .40/MIN..)"