Merged cells and formula data sources


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

Advertisements

G

Guest

Toppers said:
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.
 
G

Guest

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!
 
G

Guest

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)
 
Ad

Advertisements

G

Guest

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

Guest

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

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
 
G

Guest

Peo Sjoblom said:
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.
 
Ad

Advertisements

P

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top