Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
Don't use merged cells!
"radellaf" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
"Toppers" wrote:
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
in c1: =A1*B1
in c2: =IF($A2=0,($C1/$B1)*$B2,$A2*$B2) Copy down "radellaf" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
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" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
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" wrote in message ... "Toppers" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
"Peo Sjoblom" wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merged cells and formula data sources
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 -- Regards, Peo Sjoblom "radellaf" wrote in message ... "Peo Sjoblom" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
formula to convert raw data from several sources into relative tot | Excel Worksheet Functions | |||
Merged Cells In Data Base | Excel Discussion (Misc queries) | |||
Chart data from merged cells. | Excel Discussion (Misc queries) | |||
Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions |