Dynamic Range Average (XP)

Topic

New Reply

I have created a chart based upon a dynamic range for showing the last 7 day’s temperature. The formula is =COUNT(SHEET1!$B:$-COUNTIF(SHEET1!$B:$B,”=0″)-5. How can I create a formula which will create an average for the same 7 day period?

The X and Y range are created with an OFFSET formula based on column A. Thought that I would need to create a Z range which would calculate the average, but can’t figure it out.

Thanks.

Reply | Quote

Replies

You can use the AVERAGE function applied to the Y range.

Reply | Quote

Sorry, I am not sure what you mean. The present formula for Y range is =OFFSET(Sheet1!$A$1,Datacount,1,7,1) I tried =AVERAGE(OFFSET(Sheet1!$A$1,Datacount,1,7,1)). This gave me an average and plotted a single point on the chart. What I would like is line running across the chart for the days in question. I checked the chart properties and I have selected a line chart . What am I missing?

Reply | Quote

You only stated in your original question that you wanted to calculate the average; that’s what the formula does. If you want to plot it as a horizontal line, create 6 copies of the formula in adjacent cells, so that you have a range of 7 cells showing the same average. Then create a chart series from these 7 points.

Reply | Quote