May 30 2020 09:26 AM
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!
May 30 2020 09:59 AM
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
May 30 2020 10:18 AM - edited May 30 2020 10:37 AM
Dear@Hans Le Roy
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
May 31 2020 02:20 AM - edited May 31 2020 02:21 AM
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!