WEEKDAY Calc to exclude empty cells

[mnyankee]

I have a created a "template" spreadsheet which contains three total rows at the top. The user copies and pastes an entire month of data into the rows below total rows and the formulas do the rest. The totals use the weekday formula to sum up the data by weekday and weekend. I have my calc range to be 31 rows because that is the most a month can have. For months containing less than 31 days, the issue I have run into on my average calculations is that for the weekends, it is including empty rows in the calculation. I have done a lot of research and the weekday calc reverts to Saturday if the cell is blank and thus adds extra "days" to divide by to get the average. So my question is how can I change my formula to automatically figure out what the last row of data is in order to not include empty cells. Note this only applies to when using the average calculation. For this example I have narrowed down my data to 13 rows. My formula in D4 is: =AVERAGE(IF(WEEKDAY($A$5:$A$20,2)>5,D5:D20)). Apologies for posting a picture but I do not have rights to install XL2BB on my computer.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=AVERAGE(IF((WEEKDAY($A$5:$A$20,2)>5)*(A5:A20<>""),D5:D20))

 

[mnyankee]

Hi & welcome to MrExcel.
How about

=AVERAGE(IF((WEEKDAY($A$5:$A$20,2)>5)*(A5:A20<>""),D5:D20))

That's perfect, thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[mnyankee]

You're welcome & thanks for the feedback.

I hate to bother you again but would you mind breaking down what your formula is doing? In particular, what is the * (A5:A20<>"") doing? I think it is looking for blanks but what does the * mean?

 

[Fluff]

The * means to multiply & (A5:A20<>"") is checking the cells are not blank.