CODEWITHSUNDEEP
sortinggoogle-sheetsgoogle-sheets-formulaaveragespreadsheet
Verminous
Verminous

Reputation: 555

Average profit per day formula in 1 single cell

I want to use the solution explained here:

How to calculate an average profit per day with Google Sheets

But in just a single cell instead of having to use 4 different cells for the calculation.

I came up with this formula but it seems to me too complicated and inefficient:

=AVERAGEIF((arrayformula(if((arrayformula(if(query({A2:A, arrayformula(int(A2:A))}, "select max(Col1) where Col1 is not null group by Col2 label max(Col1) ''", 0)<>"", vlookup(query({A2:A, arrayformula(int(A2:A))}, "select max(Col1) where Col1 is not null group by Col2 label max(Col1) ''", 0), {A2:A,C2:C}, 2, false), "")))<>"", (arrayformula(if(query({A2:A, arrayformula(int(A2:A))}, "select max(Col1) where Col1 is not null group by Col2 label max(Col1) ''", 0)<>"", vlookup(query({A2:A, arrayformula(int(A2:A))}, "select max(Col1) where Col1 is not null group by Col2 label max(Col1) ''", 0), {A2:A,C2:C}, 2, false), "")))-QUERY((arrayformula(if(query({A2:A, arrayformula(int(A2:A))}, "select max(Col1) where Col1 is not null group by Col2 label max(Col1) ''", 0)<>"", vlookup(query({A2:A, arrayformula(int(A2:A))}, "select max(Col1) where Col1 is not null group by Col2 label max(Col1) ''", 0), {A2:A,C2:C}, 2, false), ""))), "SELECT * offset 1", 0), "")*-1)),">0")

Dummy file is here, formula is on D4:

https://docs.google.com/spreadsheets/d/1ExXtmQ8nyuV1o_UtabVJ-TifIbORItFMWjtN6ZlruWc/edit?usp=sharing

Any suggestions?

Upvotes: 1

Views: 80

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(AVERAGE(QUERY(IFNA(QUERY(
 INDEX(SORTN(SORT({INT(A3:A), C3:C}, ROW(A3:A), ), 9^9, 2, 1, 1),,2),  "offset 1", )-
 INDEX(SORTN(SORT({INT(A3:A), C3:C}, ROW(A3:A), ), 9^9, 2, 1, 1),,2)), "offset 1", )))

enter image description here

Upvotes: 2

Related Questions