Excel - plotting values of average-function

%3CLINGO-SUB%20id%3D%22lingo-sub-1428740%22%20slang%3D%22en-US%22%3EExcel%20-%20plotting%20values%20of%20average-function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428740%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EAs%20I%20recently%20begun%20to%20do%20some%20statistics%20regarding%20the%20active%20COVID19%20cases%20in%20my%20own%20country%2C%20I%20was%20busy%20smoothing%20out%20the%20daily%20numbers%20by%20using%20the%20%22Average%22-function%20over%20a%20period%20of%20seven%20days.%20As%20the%20numbers%20were%20falling%20rapidly%20and%20the%20corresponding%20graph%20becoming%20more%20and%20more%20horizontal%2C%20I%20noticed%20that%20it%20wasn't%20horizontal%20on%20days%20with%20identical%20numbers%2C%20but%20was%20still%20showing%20the%20right%20values%20on%20mouse-over!%20When%20I%20was%20comparing%20the%20%22average%22-column%20with%20another%20column%20with%20the%20same%20values%20manually%20entered%2C%20I%20got%20two%20almost%20identical%20graphs%2C%20but%20with%20more%20or%20less%20offset.%20Changing%20the%20chart%20type%20didn't%20help...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20advice!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1428740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428785%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20plotting%20values%20of%20average-function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428785%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684343%22%20target%3D%22_blank%22%3E%40jhemp01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26gt%3Bit%20wasn't%20horizontal%20on%20days%20with%20identical%20numbers%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20guess%20there%20is%20a%20problem%20with%20the%20reference%20in%20the%20AVERAGE%20function%20you%20use.%3C%2FP%3E%3CP%3EWthout%20seeing%20your%20sheet%2C%20I'd%20try%3C%2FP%3E%3CP%3E-%20columns%20date%20%7C%20number%20%7C%20week%20number%20(calculated)%3C%2FP%3E%3CP%3E-%20a%20second%20sheet%20with%26nbsp%3B%3CBR%20%2F%3Eweek%20number%20%7C%20average%20(calculated%20with%20AVERAGEIF)%20-%20see%26nbsp%3Bc8e2e-0dec-4308-af69-f5576d8ac642%3C%2FP%3E%3CP%3E%26nbsp%3Bor%20a%20pivot%20table%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3CP%3EHans%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428811%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20plotting%20values%20of%20average-function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428811%22%20slang%3D%22en-US%22%3E%3CP%3EDear%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F150069%22%20target%3D%22_blank%22%3E%40Hans%20Le%20Roy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hereby%20send%20you%20a%20prepared%20sheet%20with%20two%20graphs.%20Yellow%20is%20what%20is%20being%20plotted%2C%20and%20blue(manual%20values)%20is%20what%20should%20be%20plotted!%20It's%20like%20the%20average-graph%20is%20averaging%20itself.%20Hopefully%20there%20will%20be%20no%20problems%20with%20dates%20%3B)!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1429477%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20plotting%20values%20of%20average-function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429477%22%20slang%3D%22en-US%22%3E%3CP%3EOk%2C%20I%20have%20finally%20solved%20my%20issue%2C%20it's%20so%20stupid%2C%20the%20average-function%20returns%20double%20values%2C%20as%20it%20usually%20does.%20Just%20they%20are%20displayed%20as%20integers%20in%20the%20cells%20and%20as%20function%20in%20the%20upper%20field.%20Normally%20double%20values%20are%20getting%20displayed%20directly%20and%20have%20to%20be%20in-%20or%20decreased%20manually.%3C%2FP%3E%3CP%3EI%20solved%20my%20issue%20by%20adding%20a%20round-function%20to%20the%20average-function-%26gt%3B%20e.g.%20%3DROUND(AVERAGE(C72%3AC78)%3B0)%2C%20and%20the%20graph%20was%20displayed%20correctly!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20to%20those%20having%20tried%20solving%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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!

3 Replies
Highlighted

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

Highlighted

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

Highlighted

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!