Richard Turner
asked on
Excel Indirect Average Formula
I have a table in Excel with headings: Sep 18, Oct 18, Nov 18, etc. I have a total row at the bottom too.
I want to use the indirect function that used given month to define which totals to average. So for example, I might want an average of the last 3 months, or the last 6.
Depending on what I want to see, in cell E62 I have the value Jan 19 and a second cell (F62) with the value Oct 18. Both are formatted as Custom Date fields as they are generated themselves by the eDate function.
I want my indirect function to then pick these up like this:
=AVERAGE(INDIRECT("SALESBY
But I get a #REF! error.
Not quite sure what I'm doing wrong.
Thanks in advance.
Richard
I want to use the indirect function that used given month to define which totals to average. So for example, I might want an average of the last 3 months, or the last 6.
Depending on what I want to see, in cell E62 I have the value Jan 19 and a second cell (F62) with the value Oct 18. Both are formatted as Custom Date fields as they are generated themselves by the eDate function.
I want my indirect function to then pick these up like this:
=AVERAGE(INDIRECT("SALESBY
But I get a #REF! error.
Not quite sure what I'm doing wrong.
Thanks in advance.
Richard
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Norie, that worked a treat :)
Thank you for the suggestion Peter. I tried it but Excel found an error with the formula.
Thank you for the suggestion Peter. I tried it but Excel found an error with the formula.
You could also use the OFFSET function to create the range to be included in the Average.
=AVERAGE(OFFSET(Reference Point, Offset Rows, Offset Columns, Height, Width))
You would use the MATCH function to determine the position of the dates; the position of the first date would set the start position and the difference between the two matches would be number of columns to use.
=AVERAGE(OFFSET(Reference Point, Offset Rows, Offset Columns, Height, Width))
You would use the MATCH function to determine the position of the dates; the position of the first date would set the start position and the difference between the two matches would be number of columns to use.
Also as an aside, having the month as a column in a table seems out of kilter with standard data table configuration.
Normal data table config would be just one field/column entitled Date and the values in this column would then be the dates as per your headings and a value column for the monetary value.
With that config you could then use the AVERAGEIFS function with the criteria referring to the Date column and the Date cells defining the date range.
That data arrangement would also lend itself better to other functions, eg Pivot Tables or Slicers etc.
Normal data table config would be just one field/column entitled Date and the values in this column would then be the dates as per your headings and a value column for the monetary value.
With that config you could then use the AVERAGEIFS function with the criteria referring to the Date column and the Date cells defining the date range.
That data arrangement would also lend itself better to other functions, eg Pivot Tables or Slicers etc.
=AVERAGE(INDIRECT("SALESBY
The changes I made are in bold Does this help?