Exclude blank cells in weekday average formula

serchingcell

New member
Joined
Oct 31, 2011
Messages
3
Reaction score
0
Points
0
Hi,

This is my first thread and im new to excel :) I’m using below formula to result weekday average and its returning with average include blank cells. I’m not sure how to add "exclude blank cells" formula in this. But i want it to count the cells contain “0” as value.
=SUMPRODUCT((WEEKDAY($A$4:$A$26,2)<6)*B4:B26)/SUMPRODUCT((WEEKDAY($A$4:$A$26,2)<6)*1)
Please help...
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi, and welcome to the forum!

Can you upload some sample data? I'm not quite sure I follow... (click Go Advanced to attach a workbook)
 

serchingcell

New member
Joined
Oct 31, 2011
Messages
3
Reaction score
0
Points
0
sample data attached

I have attached the sample dat with description. hope it helps
 

Attachments

  • sample data.xlsx
    12.6 KB · Views: 58

serchingcell

New member
Joined
Oct 31, 2011
Messages
3
Reaction score
0
Points
0
I need weekday average

Thank you. but you have given me average exclude blanks for entire month. thats not what i wanted:confused2: Maybe i should say like this:
using the same sample data, I would like to find "weekday average exclude blank cells" formula. is this much clear?

For now im using =SUMPRODUCT((WEEKDAY($B$2:$B$32,2)<6)*C2:C32)/SUMPRODUCT((WEEKDAY($B$2:$B$32,2)<6)*1) and this one returning with blanks average
 
Top