Decimal Place goes crazy when using the "&" Sign.

S

spreadsheetlady

Hi,
Basic Formula in A1 says: B1*C1. This works.
I have all cells formatted to Number Format with 0 decimal place(s). I also
have the 1000 separator (,) check box checked.

If I change the same formula in A1 to: B1*C1&" lbs." .....A1 gives the
right answer but shows a zillion decimal places with it.

Is there a tip anyone could give me on what is happening and a suggestion
for getting rid of the decimal places?

Because of the &" lbs." does Excel maybe think the formula is text? I don't
know.

Thank-you in advance,
Amy
 
F

Fred Smith

When you use A1 in a formula, it uses the contents of the cell, and ignores
formats. Formats control only the display of that cell, and have no impact
on other formulas. To include formatting in your formula, use the Text
function, as in:
=Text(B1*C1,"#,##0)&" lbs."

Or, use the formula:
=b1*c1
and set a custom format of: #,##0" lbs"

Regards,
Fred
 
J

Jim Thomlinson

When you concatenate the lb to the end of the number you change the value to
text. Once it is text your formats don't apply. There are 2 choices. The
first is to use teh Text Formula as Teehtless mama shows (have not seen you
in a while
Teethless... good to have ya back)

The other option is to use a custom format
Format Cells... -> Number -> Custom
#,### "lb"

The benefit to the custom format is your number is still a number and it can
be used in further calculations.
 
S

spreadsheetlady

Thank-you Teethless, Jim & Fred.
The TEXT Function fixed it.
I'm also studying the Custom Formatting. I've always noticed this feature
but never persued it. Now is the time.
Appreciate it.
Amy
 

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