Extending a SUM range, based upon a cell value

[tommyarra]

Hi guys,

New here.. and relatively new to excel. So I apologise if this is an easy one that I should have figured out.

Essentially what I want to do is increase a cell range in a SUM formula, based upon a different cell value. Specifically including another row, directly above.

For example, my current formula is

=SUM(C2:E12)/1955

I would like it to extend by 1 row above, if cell E13 value is 1

So if a 1 was entered into cell E13, the calculation would be automatically adjusted to (B2:E12)/1955

And if a 2 was entered it would be (A2:E12)/1955


Not sure if this is possible...? Thanks so much, in advance

 

[awoohaw]

try this:

Book1
	A	B	C	D	E
1
2	13	8	16	5	8
3	12	9	11	16	22
4	18	17	8	18	9
5	8	4	8	12	17
6	14	8	8	11	16
7	14	18	18	25	14
8	12	9	14	18	9
9	13	8	12	13	5
10	17	17	13	12	17
11	8	4	7	21	23
12	6	8	23	24	16
13					2
14	Formula:	714
Sheet2

Cell Formulas
Range	Formula
B14	B14	=SUM(INDEX(A2:E12,1,SWITCH(E13,1,2,2,1,3)):E12)

 

[tommyarra]

I'm such a donut and I have just realised that I totally messed up my original post getting rows and columns mixed up, just about sums up my skill level!!

Let's try again... Thanks so much for your help.

Current formula in cell H62 : =SUM(B10:E61)/1955

I'd like it to look at cell K62 and if it is 1, add 1 row ABOVE...

So, change the B6 part of the formula to B9...

And if a 2 was added it would change to B8...

And if a 3 was added it would change to B7 etc etc etc

 

[Cubist]

Try:

=SUM(DROP(B1:E61,9-K62))/1955

 

[tommyarra]

Try:

=SUM(DROP(B1:E61,9-K62))/1955

Legend.. this appears to work (I'm very tired at the end of a night shift, so I'll test it thoroughly tomorrow)

Am I write in saying, if I wanted the same formula in the cell above, it would be

=SUM(DROP(B1:E60,8-K61)/1955

???

So, I want the B1 to always be B1

E60 to move up a row,
9 changes to 8
And the lookup cell moves up a row to K61

If this is correct. How do I easily copy the formula to multiple rows?

Thanks so so much. This is going to help me massively.

 

[Cubist]

Try this in H62 and copy up.

=SUM(DROP(B$1:E61,ROW(B62)-53-K62))/1955

Note the last row you can copy up is row 54 because anything beyond this is above row 1 which doesn't exist.