Cell reference in formula string displays abbreviated value


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
 
Ad

Advertisements

J

joeu2004

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.)

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.)"
 
Ad

Advertisements


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

Top