Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Range Average (XP)

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

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •