There's been similar questions answered here but I've been unable to apply those tips to my scenario. I keep data on gambling wins and loses. I want to calculate averages based on the day of the week. I should point out my spreadsheet includes dates with no data (for a reason). The formula I've been trying use for this is =AVERAGE(IF(C2:C19="Mon",B2:B19)). I've included a sample sheet. My knowledge of spreadsheets is severely limited. I know just enough to get in trouble. Thanks in advance for any help, this is my first question on the forum.
Averaging win/loss data by day in Google Sheets
formulasgoogle sheets
Related Solutions
=TEXT(A1;"dddd")
where A1 is the cell with the date.
instead of your formula in B column and the whole pivot table, use:
=ARRAY_CONSTRAIN(ARRAYFORMULA(REGEXREPLACE(QUERY(IF(LEN(A2:A),
{WEEKDAY(A2:A), TEXT(A2:A, "dddd am/pm hh:00 am/pm")}, ),
"select Col2,count(Col2),Col1
where Col2 is not null
group by Col2,Col1
order by Col1
label count(Col2)''", 0)&"AM ", "PM |AM ", "")), 999^99, 2)
Best Answer
Try
This will generate all the results in one go.