Cell reference in formula string displays abbreviated value

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

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
 
Pete said:
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.)
Click to expand...

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.)"
 
Back
Top