Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal Place goes crazy when using the "&" Sign.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal Place goes crazy when using the "&" Sign.
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 "spreadsheetlady" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal Place goes crazy when using the "&" Sign.
=TEXT(B1*C1,"#,##0")&" lbs"
"spreadsheetlady" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal Place goes crazy when using the "&" Sign.
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. -- HTH... Jim Thomlinson "spreadsheetlady" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal Place goes crazy when using the "&" Sign.
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 "Fred Smith" wrote: 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 "spreadsheetlady" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
How to "sign" macro with "digital certificate"? | New Users to Excel | |||
the "#" signs are driving me crazy :-( | New Users to Excel | |||
Rounding errors when a "5" is the third decimal place using formul | Excel Discussion (Misc queries) | |||
How do display a "+" or "-" sign when hiding columns? | Setting up and Configuration of Excel |