How to correct this formula?

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
881
Office Version
  1. 2013
  2. 2007
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top