How to add value in relative cell

Question

I want to know if there is another way of adding B1, C1 and D1 per my example below other than using a regular sum.

I want to make something like:

E1 = COLUMN(column of E1 - 1)ROW(1) + COLUMN(column of E1 - 2)ROW(1) + COLUMN(column of E1 - 3)ROW(1)

So that E1 = 4 + 3 + 2

Sorry for troubling you guys. Thanks and regards

score 2 · Answer 1 · edited May 23 '17 at 12:06

2

You can use the OFFSET formula to do this:

=SUM(OFFSET(E1,0,-3):OFFSET(E1,0,-1))

To make it generic you can follow this thread and use this formula:

=SUM(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-3):OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1))

Where the formula to get the reference to the current cell is:

=INDIRECT(ADDRESS(ROW(), COLUMN())

Which then gets substituted into the original formula for each original reference to E1.

HTH.

edited May 23 '17 at 12:06

Community

answered Oct 20 '16 at 05:10

Robin Mackenzie

Thanks. Do you happen to know if there is function to get E1 address without typing E1? – Tuss Oct 20 '16 at 05:16

score 0 · Answer 2 · edited Oct 20 '16 at 07:10

0

Putting in ="E1"&"="&B1&"+"&C1&"+"&D1 had the cell come out to E1=2+3+4. You'd just have to change E1 manually if you moved it around.

edited Oct 20 '16 at 07:10

Suren Srapyan

answered Oct 20 '16 at 05:12

C Fish

score 0 · Answer 3 · answered Oct 20 '16 at 13:41

0

put the formula in E1 : =INDIRECT(ADDRESS(ROW(),COLUMN()-3))+INDIRECT(ADDRESS(ROW(),COLUMN()-2))+INDIRECT(ADDRESS(ROW(),COLUMN()-1))

answered Oct 20 '16 at 13:41

Huzefa Hamid

3 Answers3