Merged cells and formula data sources

[radellaf]

I'm working on a sheet where I need a formula in a column that is, say, col A
* col B. Easy without merged cells.

Say that nothing in Col B is merged. But, Col A, for instance, consists of
two cells vertically merged, maybe 3 or 4 in places. So A1 and A2 are a
single cell with one number, and B1 and B2 are separate cells with different
numbers.

I want two results - A (merged) times B1 in C1, A(merged) times B2 in C2.

Excel unfortunately evaluates A2 as a zero.

Is there a way to get it to correctly find the value of entries in col A?

A cells are not always in pairs, so just referring one-row-up in every other
C formula will not work. I'm thinking there might be a function that returns
the highest value of a cell and any cells it is merged with.
like =MERGEDHIGHEST(A2) would discover A1 and A2 are merged, and return the
number in A1 rather than the phantom zero in A2.

 

[Toppers]

Don't use merged cells!

 

[radellaf]

Don't use merged cells!

What else would create the same appearance? i.e., the number in cell
A1-merge-A2 appears, and needs to be entered, once, not twice. That merging
allows the number to be centered is fine, but not essential.

 

[Toppers]

in c1: =A1*B1
in c2: =IF($A2=0,($C1/$B1)*$B2,$A2*$B2)

Copy down

 

[radellaf]

in c1: =A1*B1

in c2: =IF($A2=0,($C1/$B1)*$B2,$A2*$B2)

Copy down

=IF($A2=0,$A1*$B2,$A2*$B2) does work.

However, when it reaches areas with the triple merge
e.g., (A3/A4/A5) to equal A3*Bx
there are two problems. Copy down puts A5*B5 in C5, and the IF only looks
at two cells. Perhaps the IF could be nested and handle, say, up to 5
merged, but copy-down would still fail...

Thanks, though!

 

[Toppers]

Not my formula which was ....

=IF($A2=0,($C1/$B1)*$B2,$A2*$B2)

worked for a merged cell of 5

This is yours:

=IF($A2=0,$A1*$B2,$A2*$B2)

 

[radellaf]

Not my formula which was ....

=IF($A2=0,($C1/$B1)*$B2,$A2*$B2)

worked for a merged cell of 5

Right... I see, as long as the C above is resolved first, it... got it.

I was copying-down the PAIR of C1/C2, you meant just copy-down C2.


---------------------------------------------------------
Works splendidly! Clever inductive logic type of formula. Thank you
---------------------------------------------------------

I wasn't thinking that way (and, well, didn't know about IF). My looking at
the (largely useless, no-index, requires internet connection) help was
showing me things like VLOOKUP so I was imagining something involving a
search.

 

[radellaf]

I really needed to multiply A*B*C in D, and the other thing I needed was the
ability to tolerate a "NA" in A without getting an error. Following your
logic, this works:

=IF($A10="NA","NA",IF($A10=0,IF($D9="NA","NA",$D9/($B9*$C9)*$B10*$C10),$A10*$B10*$C10))

Doesn't allow for NA in the B or C but I guess with enough more IFs it
probably could.

 

[Peo Sjoblom]

Center across selection can in most cases replace the merged cells.
I would strongly advice against using it, to maybe gain a tiny bit when it
comes to appearance but losing a giant step when it comes to usability is
not really the best way


--
Regards,

Peo Sjoblom

 

[radellaf]

Center across selection can in most cases replace the merged cells.

I get that option for a group of horizontal cells, but in Vertical I don't
see "center across selection"

I would strongly advice against using it, to maybe gain a tiny bit when it
comes to appearance but losing a giant step when it comes to usability is
not really the best way

Well, it seems to be a bug that it's a usability problem. All refs to a
merged group should clearly return the actual value in the merged cell, not
some bogus zero.

In this case I have no option, that's the way "they" want it to look.

 

[Peo Sjoblom]

I missed the vertical thing. Nevertheless I would never use merge cells,
especially if I needed to make any sort of references to them.

It's not really a bug, since only A1 contains a value, A2 is empty and all
references to empty cells in Excel returns a zero. Of course if you unmerge
the cells you will notice that A2 is now empty. You need to "educate" the
client that merged cells should be avoided like the plague