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

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

 

[Kassie]

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

 

[Jayson]

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

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

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.