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