Google sheet adding Serial number and calculation

Question

With respect to Google Sheet: Fetch earliest date and average of value of month from the list

Raw 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

I want following with serial number in reverse order:

3. 06/2000 5.225
2. 07/2000 4.98  10.205 (*addition of value 1 and 2)
1. 08/2000 4.44  5.765  (substration of output of 2 and value of 3)

Series should follow: +,-,-,- and so on.. Please suggest with Googlefinance() function Thanks in advance. Best Regards,

add more details. its not clear how you want it to continue (+,-,+,-,...?) logic behind it, and also, such numbers may reapeat — player0, May 28 '22 at 10:24
can you explain the logic? what are you looking for? the increase and decrease in value? between befin of each month? or average? — Mike Steelson, May 28 '22 at 11:25
@Mike. I want to design algorithm for flow of data...for different predictive future value...of which this is only the part.. — SP160477, May 28 '22 at 11:29

score 2 · Accepted Answer · answered May 28 '22 at 13:16

use:

=ARRAYFORMULA({SUM(QUERY(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"),
 "select Col2 limit 2 offset 1", )); SUM(QUERY(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"),
 "select Col2 limit 2 offset 1", ))-MMULT(TRANSPOSE((SEQUENCE(ROWS(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"))-3) <=SEQUENCE(1, ROWS(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"))-3))*QUERY(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"), 
 "select Col2 offset 3", )), SEQUENCE(ROWS(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"))-3, 1, 1, ))})

note a limitation of LTE to be < 10M cells so in your case the GOOGLEFINANCE formula can output a maximum of 3162 rows. more on this in answers over here. to work with more rows you can't hardcode GOOGLEFINANCE into ARRAYFORMULA

score 1 · Answer 2 · answered May 28 '22 at 11:38

To get the difference between two consecutive rows, use MMULT as follows

=ARRAYFORMULA(if(B2:B="",,mmult(-1*(ROW(B2:B)=TRANSPOSE(ROW(B2:B)+1))+1*(ROW(B2:B)=TRANSPOSE(ROW(B2:B))),if(B2:B="",0,B2:B))))

Limit the number of rows to prevent long calculation

MMULT

MMULT builds an array as follows

Google sheet adding Serial number and calculation

2 Answers2