0

I need to create a dynamic formula using an equation based on the value of a cell, but I am struggling to find what I need despite having done this before. For example I have 600 rows in an excel, I want to check something in the last 200 rows only. I have in D1 for example 600 which relates to the amount of rows. I need to ideally do something like so:

SUM(A(D1+1-200):A(D1+1))

In other words

SUM(A401:A601)

I have tried the following as I thought the dollar sign left this as variable, but it is not working for me.

SUM($A$(D1+1-200):$A$(D1+1))

Unfortunately all searches give me results unrelated to what I am trying to achieve.

  • The dollar sign changes a columns or row reference from relative to absolute. What I think you want is INDIRECT – cybernetic.nomad Mar 14 '18 at 16:41
3

You can use INDEX, which is not volatile:

=SUM(INDEX(A:A,D1+1-200):INDEX(A:A,D1+1))
| improve this answer | |
1

Consider:

=SUM(INDIRECT("A"&(D1+1-200)&":A"&(D1+1)))

enter image description here

Which, for the case illustrated above, is the same as:

=SUM(A1:A201)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.