PDA

View Full Version : Exclude blank cells in weekday average formula



serchingcell
2011-10-31, 03:16 AM
Hi,

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.
=SUMPRODUCT((WEEKDAY($A$4:$A$26,2)<6)*B4:B26)/SUMPRODUCT((WEEKDAY($A$4:$A$26,2)<6)*1)
Please help...

Ken Puls
2011-11-01, 11:10 PM
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
2011-11-03, 03:12 AM
I have attached the sample dat with description. hope it helps

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

How's this for C34:

=AVERAGEIF(C2:C32,"<>0")

serchingcell
2011-11-03, 06: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, 06:27 AM
Try this and let me know if this works.