Too many decimals when using =[cell reference]&"[text]"

  • Thread starter Thread starter KM
  • Start date Start date
K

KM

Excel 2007: I have a formula along the lines of =[cell
reference]&"[text]"&[cell reference]&"[text]" etc which returns a summary of
the design produced by the spreadsheet e.g. "..6m length pile with 4.3m
length top section.." Occasionally the numbers returned by the formula,
taken from the cell references, are displayed with 15 decimal places. This
is despite the cell containing the formula, and the cell containing the
referenced cells, being set to display only 2dp. Moreover the referenced
cells contain a number with only 2dp; 15 should not be possible! Why is this
and how do I stop it occurring? Thanks.
 
That is because the cell may be formatted to display only two decimals, but
not to contain only 2 decimals. As such, a value of 6.435543 displays as
6.44, but is in actual fact still 6.435543. The result cell is a text
string, that takes the data from other cells, and concatenates it into a
string. As such, it takes the full cell contents, and not only the displayed
values.

To prevent this from happening, you could change the formulae in the
referenced cells to round to 2 decimal points, so that 6.435543 will round
off to 6.44, as a value.

--
HTH

Kassie

Replace xxx with hotmail
 
I would actually do the rounding in the text cell. That way no other
calculations are being affected.

KM listed the following:
=[cell reference]&"[text]"&[cell reference]&"[text]"

I would adjust so it reads like this
=round([cell reference],2)&"[text]"&round([cell reference],2)&"[text]"
--
---
Jayson
http://excelninja.blogspot.com


Kassie said:
That is because the cell may be formatted to display only two decimals, but
not to contain only 2 decimals. As such, a value of 6.435543 displays as
6.44, but is in actual fact still 6.435543. The result cell is a text
string, that takes the data from other cells, and concatenates it into a
string. As such, it takes the full cell contents, and not only the displayed
values.

To prevent this from happening, you could change the formulae in the
referenced cells to round to 2 decimal points, so that 6.435543 will round
off to 6.44, as a value.

--
HTH

Kassie

Replace xxx with hotmail


KM said:
Excel 2007: I have a formula along the lines of =[cell
reference]&"[text]"&[cell reference]&"[text]" etc which returns a summary of
the design produced by the spreadsheet e.g. "..6m length pile with 4.3m
length top section.." Occasionally the numbers returned by the formula,
taken from the cell references, are displayed with 15 decimal places. This
is despite the cell containing the formula, and the cell containing the
referenced cells, being set to display only 2dp. Moreover the referenced
cells contain a number with only 2dp; 15 should not be possible! Why is this
and how do I stop it occurring? Thanks.
Click to expand...
Click to expand...
 
Back
Top