Variable reference to add cells

[Nick Savage]

I want to use a variable to add a number of cell values together:

in the example below cell 'e3' needs to be the sum of cells a1:a2 less the
sum of cells a4:e5 but I need to use the value of cell 'a5' to 'go back' 4
columns so that I can vary the number of columns to count back by changing
the value of a5.

a b c d e
1 10

2 20

3

4 2 2 4 2 2

5 4

Thanks

 

[Jim Thomlinson]

If I understand your question correctly this should be close...

=SUM(A1:A2)-SUM(A4:INDEX(4:4, 1, A5+1))

 

[Nick Savage]

Thanks Jim, I think I need to explain better:

I need a1:a2 and a4:e4 to be referenced from e3, using the value in a5
(the rows will always be 1 & 2 and 4 but the columns need to be dependent
on the value of a5 so

if a5 was 4 the answer would be 20 (a1:a2) - (a4:e4)

and if a5 was 3 the answer would be 12 (b1:b2) - (b4:e4)

key is to change a5 and have different answers without changing formula



Nick

 

[Jim Thomlinson]

=INDEX(A1:E1,1,5-A5)+INDEX(A2:E2,1,5-A5)-SUM(INDEX(A4:E4,1,5-A5):E4)

 

[Nick Savage]

Wow!!

Cheers Jim, a great help
--
Nick

=INDEX(A1:E1,1,5-A5)+INDEX(A2:E2,1,5-A5)-SUM(INDEX(A4:E4,1,5-A5):E4)