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 sev...
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