View Full Version : Exclude blank cells in weekday average formula

2011-10-31, 04:16 AM

This is my first thread and im new to excel :) Im using below formula to result weekday average and its returning with average include blank cells. Im not sure how to add "exclude blank cells" formula in this. But i want it to count the cells contain 0 as value.
Please help...

Ken Puls
2011-11-02, 12:10 AM
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)

2011-11-03, 04:12 AM
I have attached the sample dat with description. hope it helps

Ken Puls
2011-11-03, 06:45 AM
Ah, okay.

How's this for C34:


2011-11-03, 07:12 AM
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

Ken Puls
2011-11-03, 07:27 AM
Try this and let me know if this works.