How to correct this formula?

[michaelsmith559]

In column bx starting in row 2, I have letters listed sequentially, starting with the letter V. Here is the formula I currently have in BR2: "=AVERAGE(V$2:INDEX(V:V,$BV$2))"

I am wanting to have this formula copied down but increment to the next letter as it is copied down. So the formula in BR3 would be "=AVERAGE(W$2:Index(W:W,$BV$2))"

I have tried using average with indirect and have used the evaluate formula to try and fix this, but to no avail. Here is the formula I am using with indirect:
=AVERAGE(INDIRECT(BX2&"$"&ROW(BX2)):INDEX((INDIRECT("BX2")&":"&INDIRECT("BX2")),$BV$2))

This formula results in :"#VALUE!" error.

Any help will be appreciated. Thanks.

Mike

 

[easy2understandexcel]

Hi,
I think you are mixing up the purpose of indirect a bit. Indirect will point to the address of the value of that cell.

For example, if you had A1 in cell X1, and A1 had "Hello World", when you put a formula =X1, the value would be hello world (because thats the value of A1).

You are on the right track with evaluate, but it only works as a named range (for some unknown reason).
But you can still use it, setup your evaluate formula as a named range (lets call it "mrExcel"), then you put =mrExcel and it returns the result.


So make your formula in BR2 --> ="=AVERAGE("&BX2&"$"&ROW(BX2)&":INDEX("&BX2&":"&BX2&",$BV$2))"
And in this example in BQ2 setup a named range =EVALUATE(BR2)

 

[texasalynn]

does this work
=AVERAGE(V$2:INDEX(OFFSET($A$1,ROW(),ROW(V21)),$BV$2))

 

[Aladin Akyurek]

In BR2 enter and copy down:

=AVERAGE(OFFSET(V$2,0,ROWS($BR$2:BR2)-1,$BV$2))

Does this meet your intent?

 

[easy2understandexcel]

Aladin's will work too and is simpler, but I wanted to explain indirect and evaluate since asked so you will know next time. Good luck!

 

[michaelsmith559]

None of the solutions above worked. When I use the evaluate formula for this formula:
=AVERAGE(INDIRECT(BX2&"$"&ROW(BX2)):INDEX((INDIRECT("BX2")&":"&INDIRECT("BX2")),$BV$2))

Everything evaluates correctly except for the last part. When I evaluate the formula here is how it evaluates it:
"AVERAGE($V$2:INDEX("V:V",33))"

Once at this point, the evaluate formula underlines "INDEX("V:V",33))"<strike></strike> and then it errors to #VALUE!
This is very close to the formula I currently have in BR2 (see post #1). At this point I am not sure what the problem is, or how to correct it.

 

[Aladin Akyurek]

None of the solutions above worked. [...]

Possibly. In case it's not implemented properly, see: https://dl.dropboxusercontent.com/u/65698317/michaelsmith559 average offset.xlsx

 

[michaelsmith559]

Your formula works great. Thanks for the help.

Mike

 

[Aladin Akyurek]

Your formula works great. Thanks for the help.

Mike

Glad to help and a happy new year.