Skipping Complimentary Cells

[ancog]

Is there a way to make a sequence of cells, in which each cell is the sum of every cell in another sequence of cells except for a complementary cell in the other sequence without subtracting the complementary cell from the sum of all the cells in the sequence? For instance is it possible to have B1 be the sum of every cell in the sequence A1 through A1000 except for A1, B2 be the sum of every cell in the sequence A1 through A1000 except for B2, B3 be the sum of every cell in the sequence A1 through A1000 except for A3, and so on to B1000 being the sum of every cell in the sequence A1 through A1000 except for A1000, so that every cell in the series B1 though B1000 is the sum of every cell in the series A1 through A1000 except for the cell in the series that is in the same row as it is?

I have attached an excel file with examples to help clarify what I am asking for. On both sheets column B and D contain the sums, although on sheet 1 the sums are simple sums while on sheet 2 the sums are in the form of an array function.

[Keebellah]

Hi, I haven't downloaded the file but based upon the your (difficult to read) explanation you might try something like this
The is the SUMIFS() function to add a range using criteria from anothe range, or just SUM() for the entire range - the result of another sum
something like

Please Login or Register  to view this content.

I think if you play around with the idea you might get what you want.

[leelnich]

Hi all- I agree w @Keebellah's approach - SUM the entire range, then subtract the same-row cell. Paste this in B2 and copy down:

Please Login or Register  to view this content.

...and this in D2 and copy down:

Please Login or Register  to view this content.

[AliGW]

OT, and purely to do with semantics (and ambiguity), please read this: http://www.write.com/writing-guides/...us-compliment/

[kev_]

I do not know why it should matter how the value is arrived at, but you requested a calculation "without subtracting the complimentary cell from the sum of all the cell"

So try this in B2 copy down:
=SUM($A$1:OFFSET($A1000,-1,0)) + (IF(ROW()=1000,0,SUM(,OFFSET($A1000,1,0):$A$1000)))
(Above formula will require adapting if cell A1 contains anything other than text)
May be more useful to make the formula more general and avoid IF(ROW() condition
with:
=SUM($A$1:OFFSET($A2,-1,0))+SUM(OFFSET($A2,1,0):$A$1048576)
OR
=SUM($A$1:OFFSET($A2,-1,0),OFFSET($A2,1,0):$A$1048576)
Cell B1048576 will return an error - but I do not think you will be using that cell


Best to ignore everything above
It is easy to look stupid sometimes ....
I forgot to simplify when I changed my approach ......
Thank you @leelnich. (see post #6)

in B2 copy down:
=SUM($A$1:$A1,$A3:$A$1048576)







EDIT
formula amended

[leelnich]

Hi all- Is it me, or was post#1 changed without a Last edited by... stamp? I don't remember the prohibition against subtraction of same-row cell. Oh well, here's a different implementation. Paste this in B2 and copy down:

Please Login or Register  to view this content.

@kev_, why use OFFSET()? Doesn't relative addressing do the same thing?

[kev_]

why use OFFSET()? Doesn't relative addressing do the same thing?

This was a legacy of a change of approach
- my original formula needed to refer to cell above
- Excel refuses to accept =A0 (row zero)
- But Excel does accept OFFSET(A1,-1,0) and the formula can be copied down
- wrap IFERROR around it solves problem with first row
etc....

I will amend post#5 again

[leelnich]

- Excel refuses to accept =A0 (row zero)
- But Excel does accept OFFSET(A1,-1,0) and the formula can be copied down
- wrap IFERROR around it solves problem with first row

Interesting! You could also paste this in B2, and copy UP:

Please Login or Register  to view this content.

...or just use a different formula for row 1.
BTW, thanks for the rep!

[kev_]

@leelnich
Copying up - why didn't I think of that

[leelnich]

Probably because you're a busy guy w better things to do!
@ancog- does this solve your problem?

[ancog]

I tested the suggestion from kev of using the formula =SUM($A$1:$A1,$A3:$A$1048576) and found that it works exactly as I was hoping it would work. I found that I could modify it for a sum of functions of cells and will work even when the complementary cell would otherwise cause the function to be undefined or when the complementary cell would produce a division by 0.

Thank You!

[kev_]

You are welcome - thanks for the rep