Merging Cells that contain data anomaly

[Edelmundo]

has anyone come accross an anomaly like this before ?

I have a spreadsheet the has a 2 cells that have been merged i have not mergered the cells i received the spreadsheet with the cells already merged, both cells contain a value. One contains the value 40 and the other 65. When the merged cell is clicked the sum view in the bottom right of the window shows the value as 105.0 I have read on microsoft's site etc that when cells containing data are merged the the value is taken from the top left cell and the other data is "deleted", this has not happened with this merge. If i unmerge the cell and then remerge the cell then the cells are merged correctly and the sum does not show 105 as the values have been discarded.

I cannot replicate the incorrect merge ??

[Dave Peterson]

There was a discussion of this a few weeks/months ago.

I think it was David McRitchie who came up with how to duplicate this:

Try this in a test worksheet

Select A1:B5
type:
=row()*Column()
(you can convert to values or keep them as formulas)

Type this in C1:
=sum(A1:B1)
and drag down to C5.

You should see something like:

1 2 3
2 4 6
3 6 9
4 8 12
5 10 15


Now select A1:B1
Format|Cells|Alignment tab|check merge cells
(Click ok to dismiss the warning).

You'll see something like:

1 1
2 4 6
3 6 9
4 8 12
5 10 15
(C1 evaluates to what you expect)

Now, select A1:B1 and hit the format painter on the formatting toolbar.
And paint over A2:B5

I see something like:
1 1
4 6
6 9
8 12
10 15

In fact, if I put this in A11
=A1
And drag down and to the right (to fill A11:A15), I see:

1 0 1
2 4 6
3 6 9
4 8 12
5 10 15

So those "merged values" that you thought were gone are still there!

And if you select A1:B5 and do Format|Cells|Alignment tab and uncheck Merge
Cells, you'll see:

1 1
2 4 6
3 6 9
4 8 12
5 10 15

=======
Copying and pasting into NotePad will reveal those other "hidden" values, too!

What this means to me is that it's just another reason not to use Merged cells.
But if I have to use them and I want my arithmetic to turn out the way I expect,
then I should not use the Format Painter icon.



Edelmundo wrote:
>
> has anyone come accross an anomaly like this before ?
>
> I have a spreadsheet the has a 2 cells that have been merged i have not
> mergered the cells i received the spreadsheet with the cells already
> merged, both cells contain a value. One contains the value 40 and the
> other 65. When the merged cell is clicked the sum view in the bottom
> right of the window shows the value as 105.0 I have read on
> microsoft's site etc that when cells containing data are merged the the
> value is taken from the top left cell and the other data is "deleted",
> this has not happened with this merge. If i unmerge the cell and then
> remerge the cell then the cells are merged correctly and the sum does
> not show 105 as the values have been discarded.
>
> I cannot replicate the incorrect merge ??
>
>
>
> --
> Edelmundo
> ------------------------------------------------------------------------
> Edelmundo's Profile: http://www.excelforum.com/member.php...o&userid=35377
> View this thread: http://www.excelforum.com/showthread...hreadid=551483

--

Dave Peterson