How to correct this formula?

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
842
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

easy2understandexcel

Active Member
Joined
Dec 26, 2012
Messages
299
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:

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
does this work
=AVERAGE(V$2:INDEX(OFFSET($A$1,ROW(),ROW(V21)),$BV$2))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
In BR2 enter and copy down:

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

Does this meet your intent?
 

easy2understandexcel

Active Member
Joined
Dec 26, 2012
Messages
299

ADVERTISEMENT

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

Well-known Member
Joined
Oct 6, 2013
Messages
842
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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
Top