Forum Discussion

jhemp01's avatar
jhemp01
Copper Contributor
May 30, 2020

Excel - plotting values of average-function

Hello,

As I recently begun to do some statistics regarding the active COVID19 cases in my own country, I was busy smoothing out the daily numbers by using the "Average"-function over a period of seven days. As the numbers were falling rapidly and the corresponding graph becoming more and more horizontal, I noticed that it wasn't horizontal on days with identical numbers, but was still showing the right values on mouse-over! When I was comparing the "average"-column with another column with the same values manually entered, I got two almost identical graphs, but with more or less offset. Changing the chart type didn't help...

 

Thanks in advance for any advice!

office 365
  • hansleroy's avatar
    hansleroy
    Iron Contributor
    May 30, 2020

    Hi jhemp01 

    >it wasn't horizontal on days with identical numbers

    I guess there is a problem with the reference in the AVERAGE function you use.

    Wthout seeing your sheet, I'd try

    - columns date | number | week number (calculated)

    - a second sheet with 
    week number | average (calculated with AVERAGEIF) - see c8e2e-0dec-4308-af69-f5576d8ac642

     or a pivot table

    Kind regards

    Hans

    • jhemp01's avatar
      jhemp01
      Copper Contributor
      May 30, 2020

      Dearhansleroy 

       

      I hereby send you a prepared sheet with two graphs. Yellow is what is being plotted, and blue(manual values) is what should be plotted! It's like the average-graph is averaging itself. Hopefully there will be no problems with dates ;)!

       

      Greetings

      • jhemp01's avatar
        jhemp01
        Copper Contributor
        May 31, 2020

        Ok, I have finally solved my issue, it's so stupid, the average-function returns double values, as it usually does. Just they are displayed as integers in the cells and as function in the upper field. Normally double values are getting displayed directly and have to be in- or decreased manually.

        I solved my issue by adding a round-function to the average-function-> e.g. =ROUND(AVERAGE(C72:C78);0), and the graph was displayed correctly!

         

        Thanks to those having tried solving it!

Resources