Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.)" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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..)" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill a cell with abbreviated text from a list | Excel Programming | |||
Formula to reference another sheet using a string in a cell | Excel Programming | |||
Formula Displays in One Cell | Excel Worksheet Functions | |||
Dynamically-linked formula, cell reference in the string | Excel Worksheet Functions | |||
Formula window displays correct answer while cell displays incorre | Excel Worksheet Functions |