Hello, I have created a dynamic calendar that references values on a separate sheet.
Date rows (8,11,114,17,20,23) show the day of the month =DATEVALUE("1-"&A2&"-"&B2)-WEEKDAY(DATEVALUE("1-"&A2&"-"&B2),3)
Sales rows (10,13,16,19,22,25) show the sales from Sales sheet and are dynamic based on the date =IF(DAY(B8)=1,(Sales!$C$10),IF((AND((DAY(B8)>1),(DAY(B8)<7))),OFFSET(Sales!$C$10,((DAY(B8)*10)-10),0),0))
What I would like is to have a sales average for each day of the week. SUM of the 6 cells possible, then divided by the number of actual days in the month (Feb 2020 has 4 Mondays, but March has 5).
I have tried using COUNT/IF/A/IFS/BLANK and AVERAGE/A/IF/IFS and can not figure out a way to do it. I Tried selecting the Range B8:B25 and selecting each of the 6 possible cells B10,B13,B16,B19,B22,B25
Some fails:
=COUNTIF((B10,B13,B16,B19,B22,B25),"<>0") = #VALUE!
=COUNTIF((B8:B25),"<>0") = #VALUE!
=AVERAGEA(B10,B13,B16,B19,B22,B25) = Wrong Result
Any help would be great.
Thanks
Bookmarks