Formula with Indirect in Average function

Rastaman

Board Regular
Joined
Feb 18, 2006
Messages
236
Hey all, I'm hoping someone can help me figure out why my formula isn't working.

=AVERAGE(INDIRECT("R"&MATCH("FA Actual",$A$1:$A$124,0)&"C"&$J$3-$J$1+15&":R"&MATCH("FA Actual",$A$1:$A$124,0)&"C"&$J$3+14&",R"&MATCH("FA Forecast",$A$1:$A$124,0)&"C"&$J$3+15,FALSE))

The intent is to average two ranges. Cells J1 and J3 contain values to adjust the columns as new data is added (13 and 33 in this example). The Match is used to get the correct row numbers. In this example the result should be equivalent to this formula:

=AVERAGE(AI34:AU34,AV36)

However, I'm getting a #REF error. The formula works if I eliminate the second term of the Average statement, as follows:

=AVERAGE(INDIRECT("R"&MATCH("FA Actual",$A$1:$A$124,0)&"C"&$J$3-$J$1+15&":R"&MATCH("FA Actual",$A$1:$A$124,0)&"C"&$J$3+14,FALSE))

When I debug with the formula auditor all the string constructs look correct, but it errors on the last operation of doing the average function.

Thanks in advance for your help.

Rick
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi:
Don't you need indirect in there twice? Once to create the first range reference, and once to create the second range reference (giving you the two arguments for AVERAGE).

HTH
 
Upvote 0
Yep, that did it. I had tried that before, just guess I didn't get the syntax right. I was thinking I needed only one Indirect for everything inside the Average parenthesis. Here's the working formula. Rather complex, unfortunately.

=AVERAGE(INDIRECT("R"&MATCH("FA Actual",$A$1:$A$124,0)&"C"&$J$3-$J$1+15&":R"&MATCH("FA Actual",$A$1:$A$124,0)&"C"&$J$3+14,FALSE),INDIRECT("R"&MATCH("FA Forecast",$A$1:$A$124,0)&"C"&$J$3+15,FALSE))

Thanks for the help,
Rick
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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