Merged cells and formula data sources

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
Don't use merged cells!
Click to expand...

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.
 
in c1: =A1*B1
in c2: =IF($A2=0,($C1/$B1)*$B2,$A2*$B2)

Copy down
Click to expand...

=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!
 
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)
 
Not my formula which was ....
=IF($A2=0,($C1/$B1)*$B2,$A2*$B2)

worked for a merged cell of 5
Click to expand...

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.
 
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.
 
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
 
Peo Sjoblom said:
Center across selection can in most cases replace the merged cells.
Click to expand...

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
Click to expand...

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.
 
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
 
Back
Top