Home |
Search |
Today's Posts |
#1
|
|||
|
|||
array error
I am having trouble with an array. I have a summary set of cells and four
identical sets that need to sum into it (to be clear, I need each of the summary array cells to sum their respective cells from the four identical areas). I am selecting the sum cell group, entering 'Sum(first range, second range, third range, fourth range)' (with none of those being named ranges...just starting cell to ending cell), and then doing ctrl-shift-enter. The result I am getting is the same in each of the sum cells, even though the input cells are clearly all different. What am I doing wrong? -- Boris |
#2
|
|||
|
|||
array error
That is not an array formula, the result is correct, you are asking the
formula to sum all ranges. For instance if you have 1 2 3 4 5 6 7 8 in A1:A8 and you select B1:B2 and want the sum of A1:A4 in B1 and A5:A8 in B2 you can use =SUM(OFFSET($A$1,{0;4},,4,)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "BorisS" wrote in message ... I am having trouble with an array. I have a summary set of cells and four identical sets that need to sum into it (to be clear, I need each of the summary array cells to sum their respective cells from the four identical areas). I am selecting the sum cell group, entering 'Sum(first range, second range, third range, fourth range)' (with none of those being named ranges...just starting cell to ending cell), and then doing ctrl-shift-enter. The result I am getting is the same in each of the sum cells, even though the input cells are clearly all different. What am I doing wrong? -- Boris |
#3
|
|||
|
|||
array error
Peo, thanks for the assistance. I am a little confused on your example, so
let me restate mine. If you can tell me directly what I should do, it would be great: A1:A5 1 2 3 4 5 B1:B5 6 7 8 9 10 I need for C1:C5 to add each of A1+B1, A2+B2, etc., so that C1:C5 look like 7 9 11 13 15 Thanks for any further assistance. -- Boris "Peo Sjoblom" wrote: That is not an array formula, the result is correct, you are asking the formula to sum all ranges. For instance if you have 1 2 3 4 5 6 7 8 in A1:A8 and you select B1:B2 and want the sum of A1:A4 in B1 and A5:A8 in B2 you can use =SUM(OFFSET($A$1,{0;4},,4,)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "BorisS" wrote in message ... I am having trouble with an array. I have a summary set of cells and four identical sets that need to sum into it (to be clear, I need each of the summary array cells to sum their respective cells from the four identical areas). I am selecting the sum cell group, entering 'Sum(first range, second range, third range, fourth range)' (with none of those being named ranges...just starting cell to ending cell), and then doing ctrl-shift-enter. The result I am getting is the same in each of the sum cells, even though the input cells are clearly all different. What am I doing wrong? -- Boris |
#4
|
|||
|
|||
array error
Maybe I'm missing something here, but what reason do you have for not simply
entering: =A1+B1 Into C1, and then drag down (using the fill handle) to copy to C5? OR, as an alternative, Select C1 to C5, with the focus in C1 (colored white), And enter =A1+B1 And then hold <Ctrl, and hit <Enter, Which will give you the exact same results (5 individual formulas) as the "drag to copy" method. Are you leaving something out of your explanation? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "BorisS" wrote in message ... Peo, thanks for the assistance. I am a little confused on your example, so let me restate mine. If you can tell me directly what I should do, it would be great: A1:A5 1 2 3 4 5 B1:B5 6 7 8 9 10 I need for C1:C5 to add each of A1+B1, A2+B2, etc., so that C1:C5 look like 7 9 11 13 15 Thanks for any further assistance. -- Boris "Peo Sjoblom" wrote: That is not an array formula, the result is correct, you are asking the formula to sum all ranges. For instance if you have 1 2 3 4 5 6 7 8 in A1:A8 and you select B1:B2 and want the sum of A1:A4 in B1 and A5:A8 in B2 you can use =SUM(OFFSET($A$1,{0;4},,4,)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "BorisS" wrote in message ... I am having trouble with an array. I have a summary set of cells and four identical sets that need to sum into it (to be clear, I need each of the summary array cells to sum their respective cells from the four identical areas). I am selecting the sum cell group, entering 'Sum(first range, second range, third range, fourth range)' (with none of those being named ranges...just starting cell to ending cell), and then doing ctrl-shift-enter. The result I am getting is the same in each of the sum cells, even though the input cells are clearly all different. What am I doing wrong? -- Boris |
#5
|
|||
|
|||
array error
No. The area I am doing this with is much larger, and for some reason I
thought that I needed to do an array to get the whole thing done in one shot. But now that I think about it, I think you're right in that all I had to do was do the ctrl-enter and I would get the same relative formula throughout. Thanks for hitting me on the back of the head so I'd wake up. :) -- Boris "RagDyer" wrote: Maybe I'm missing something here, but what reason do you have for not simply entering: =A1+B1 Into C1, and then drag down (using the fill handle) to copy to C5? OR, as an alternative, Select C1 to C5, with the focus in C1 (colored white), And enter =A1+B1 And then hold <Ctrl, and hit <Enter, Which will give you the exact same results (5 individual formulas) as the "drag to copy" method. Are you leaving something out of your explanation? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "BorisS" wrote in message ... Peo, thanks for the assistance. I am a little confused on your example, so let me restate mine. If you can tell me directly what I should do, it would be great: A1:A5 1 2 3 4 5 B1:B5 6 7 8 9 10 I need for C1:C5 to add each of A1+B1, A2+B2, etc., so that C1:C5 look like 7 9 11 13 15 Thanks for any further assistance. -- Boris "Peo Sjoblom" wrote: That is not an array formula, the result is correct, you are asking the formula to sum all ranges. For instance if you have 1 2 3 4 5 6 7 8 in A1:A8 and you select B1:B2 and want the sum of A1:A4 in B1 and A5:A8 in B2 you can use =SUM(OFFSET($A$1,{0;4},,4,)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "BorisS" wrote in message ... I am having trouble with an array. I have a summary set of cells and four identical sets that need to sum into it (to be clear, I need each of the summary array cells to sum their respective cells from the four identical areas). I am selecting the sum cell group, entering 'Sum(first range, second range, third range, fourth range)' (with none of those being named ranges...just starting cell to ending cell), and then doing ctrl-shift-enter. The result I am getting is the same in each of the sum cells, even though the input cells are clearly all different. What am I doing wrong? -- Boris |
#6
|
|||
|
|||
array error
Thanks for the feed-back ... and I hope I didn't make too big of a bump.<bg
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "BorisS" wrote in message ... No. The area I am doing this with is much larger, and for some reason I thought that I needed to do an array to get the whole thing done in one shot. But now that I think about it, I think you're right in that all I had to do was do the ctrl-enter and I would get the same relative formula throughout. Thanks for hitting me on the back of the head so I'd wake up. :) -- Boris "RagDyer" wrote: Maybe I'm missing something here, but what reason do you have for not simply entering: =A1+B1 Into C1, and then drag down (using the fill handle) to copy to C5? OR, as an alternative, Select C1 to C5, with the focus in C1 (colored white), And enter =A1+B1 And then hold <Ctrl, and hit <Enter, Which will give you the exact same results (5 individual formulas) as the "drag to copy" method. Are you leaving something out of your explanation? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "BorisS" wrote in message ... Peo, thanks for the assistance. I am a little confused on your example, so let me restate mine. If you can tell me directly what I should do, it would be great: A1:A5 1 2 3 4 5 B1:B5 6 7 8 9 10 I need for C1:C5 to add each of A1+B1, A2+B2, etc., so that C1:C5 look like 7 9 11 13 15 Thanks for any further assistance. -- Boris "Peo Sjoblom" wrote: That is not an array formula, the result is correct, you are asking the formula to sum all ranges. For instance if you have 1 2 3 4 5 6 7 8 in A1:A8 and you select B1:B2 and want the sum of A1:A4 in B1 and A5:A8 in B2 you can use =SUM(OFFSET($A$1,{0;4},,4,)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "BorisS" wrote in message ... I am having trouble with an array. I have a summary set of cells and four identical sets that need to sum into it (to be clear, I need each of the summary array cells to sum their respective cells from the four identical areas). I am selecting the sum cell group, entering 'Sum(first range, second range, third range, fourth range)' (with none of those being named ranges...just starting cell to ending cell), and then doing ctrl-shift-enter. The result I am getting is the same in each of the sum cells, even though the input cells are clearly all different. What am I doing wrong? -- Boris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplifying array formula which evaluates as error. | Excel Worksheet Functions | |||
Error 1004 using array in Sheet select. | Excel Discussion (Misc queries) | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) | |||
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) |