Home > Enterprise >  Google Sheet: Fetch earliest date and average of value of month from the list
Google Sheet: Fetch earliest date and average of value of month from the list

Time:05-26

Below is the data:

02/06/2000 15:30:00 5.12
05/06/2000 15:30:00 5.2
06/06/2000 15:30:00 5.28
07/06/2000 15:30:00 5.26
08/06/2000 15:30:00 5.33
09/06/2000 15:30:00 5.36
12/06/2000 15:30:00 5.24
13/06/2000 15:30:00 5.33
14/06/2000 15:30:00 5.39
15/06/2000 15:30:00 5.3
16/06/2000 15:30:00 5.29
19/06/2000 15:30:00 5.27
20/06/2000 15:30:00 5.26
21/06/2000 15:30:00 5.33
22/06/2000 15:30:00 5.25
23/06/2000 15:30:00 5.15
26/06/2000 15:30:00 5.21
27/06/2000 15:30:00 5.15
28/06/2000 15:30:00 5.09
29/06/2000 15:30:00 5.11
30/06/2000 15:30:00 4.82
03/07/2000 15:30:00 4.87
04/07/2000 15:30:00 4.92
05/07/2000 15:30:00 4.87
06/07/2000 15:30:00 4.93
07/07/2000 15:30:00 4.85
10/07/2000 15:30:00 5.2
11/07/2000 15:30:00 5.43
12/07/2000 15:30:00 5.54
13/07/2000 15:30:00 5.29
14/07/2000 15:30:00 5.17
17/07/2000 15:30:00 5.29
18/07/2000 15:30:00 5.07
19/07/2000 15:30:00 5.05
20/07/2000 15:30:00 4.92
21/07/2000 15:30:00 5.04
24/07/2000 15:30:00 4.77
25/07/2000 15:30:00 4.91
26/07/2000 15:30:00 4.62
27/07/2000 15:30:00 4.8
28/07/2000 15:30:00 4.59
31/07/2000 15:30:00 4.47
01/08/2000 15:30:00 4.57
02/08/2000 15:30:00 4.51
03/08/2000 15:30:00 4.57
04/08/2000 15:30:00 4.41
07/08/2000 15:30:00 4.3
08/08/2000 15:30:00 4.35
09/08/2000 15:30:00 4.35
10/08/2000 15:30:00 4.33
11/08/2000 15:30:00 4.3
14/08/2000 15:30:00 4.26
16/08/2000 15:30:00 4.26
17/08/2000 15:30:00 4.25
18/08/2000 15:30:00 4.28
21/08/2000 15:30:00 4.36
22/08/2000 15:30:00 4.39
23/08/2000 15:30:00 4.44
24/08/2000 15:30:00 4.45
25/08/2000 15:30:00 4.52
28/08/2000 15:30:00 4.68
29/08/2000 15:30:00 4.71
30/08/2000 15:30:00 4.68
31/08/2000 15:30:00 4.79

I want to have an average of the individual months like:

06/20000 5.225
07/2000 4.98
08/2000 4.44

Right now, I am using multiple columns which include multiple fucntions for getting this data. Query by using googlefinance function raw data will be highly appreciable. Thanks in advance. Best Regards

CodePudding user response:

You can simply do it by a pivot table

Group dates by year-month

enter image description here

CodePudding user response:

try:

=ARRAYFORMULA(SORTN(TEXT(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"), 
 {"e/mm", "#"}), 9^9, 2, 1, 0))

enter image description here

  • Related