# Formula with Indirect in Average function

#### Rastaman

##### Board Regular
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.

Rick

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### xenou

##### MrExcel MVP
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

#### Rastaman

##### Board Regular
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

Replies
8
Views
117
Replies
0
Views
330
Replies
0
Views
419
Replies
5
Views
207
Replies
3
Views
189

1,191,719
Messages
5,988,284
Members
440,148
Latest member
sandy123

### 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