1. Dynamic Range Average (XP)

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.

2. Re: Dynamic Range Average (XP)

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

3. Re: Dynamic Range Average (XP)

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?

4. Re: Dynamic Range Average (XP)

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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•