# How to correct this formula?

#### michaelsmith559

##### Well-known Member
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

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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)

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

In BR2 enter and copy down:

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

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!

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.

Your formula works great. Thanks for the help.

Mike

Your formula works great. Thanks for the help.

Mike

Glad to help and a happy new year.

Replies
7
Views
161
Replies
5
Views
169
Replies
3
Views
237
Replies
3
Views
212
Replies
2
Views
221

1,217,347
Messages
6,136,046
Members
449,984
Latest member
Ffprojectjkt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back