When Averaging a column, exclude value based on another cell value

[Guest]

Column C contains the day of the Week. Column D contains the clock-in time.
Column E contains the end of the day clock-out time. I am currently using
the formula =Average(D).

When averaging a column of clock-in times, can the values for Saturday &
Sunday be excluded? I'm using column notation because the length of the
column is variable.

Thanks,

Charlie

 

[Pete_UK]

Try this:

=AVERAGE(IF((C:C<>"Saturday")*(C:C<>"Sunday"),D))

As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly then Excel will add curly braces { }
around the formula - you must not type these yourself.

Do not place the formula in column C or column D.

Hope this helps.

Pete

 

[Pete_UK]

Try this:

=AVERAGE(IF((C:C<>"Saturday")*(C:C<>"Sunday"),D))

As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly then Excel will add curly braces { }
around the formula - you must not type these yourself.

Do not place the formula in column C or column D.

Hope this helps.

Pete

 

[Guest]

I pasted the formula into the cell and used Ctrl-Shift-Enter. Excel placed
the curly brackets around the formula, but the result is #NUM!. When I used
the evaluation tool on the formula, the C:C<>"Saturday" was replaced with
#NUM!, then when I continued with the evaluation C:C<>"Sunday" was replaces
with #NUM!. On the next click of the evaluation button the #NUM!*#NUM! was
replaced with #NUM!. And subsequently, the whole formula returned #NUM!.

Thanks for the attempt. Do you have any other ideas?

 

[Guest]

I pasted the formula into the cell and used Ctrl-Shift-Enter. Excel placed
the curly brackets around the formula, but the result is #NUM!. When I used
the evaluation tool on the formula, the C:C<>"Saturday" was replaced with
#NUM!, then when I continued with the evaluation C:C<>"Sunday" was replaces
with #NUM!. On the next click of the evaluation button the #NUM!*#NUM! was
replaced with #NUM!. And subsequently, the whole formula returned #NUM!.

Thanks for the attempt. Do you have any other ideas?

 

[Pete_UK]

Presumably column references won't work - try amending it as follows:

=AVERAGE(IF((C1:C65522<>"Saturday")*(C1:C65522<>"Sunday"),D165522))

This is almost a complete column. Again, CSE to commit the formula.

Hope this helps.

Pete

 

[Pete_UK]

Presumably column references won't work - try amending it as follows:

=AVERAGE(IF((C1:C65522<>"Saturday")*(C1:C65522<>"Sunday"),D165522))

This is almost a complete column. Again, CSE to commit the formula.

Hope this helps.

Pete

 

[Guest]

Hmmmm...when I pasted the formula into the cell it calculated the average
beginning time as 12:03 AM. When I changed 65522 to 1000, it calculated the
time as 4:18. When I changed it to the exact number of lines I had (565) it
came up with the same answer as =Average(D) did - 7:49 AM. Interesting...

If you have any other ideas I'd like to hear them. I'll keep looking for
what I can...just scratching the surface compared to the ideas you've
presented.

Thanks again!

 

[Guest]

Hmmmm...when I pasted the formula into the cell it calculated the average
beginning time as 12:03 AM. When I changed 65522 to 1000, it calculated the
time as 4:18. When I changed it to the exact number of lines I had (565) it
came up with the same answer as =Average(D) did - 7:49 AM. Interesting...

If you have any other ideas I'd like to hear them. I'll keep looking for
what I can...just scratching the surface compared to the ideas you've
presented.

Thanks again!

 

[Guest]

Pete,

I wound up using this formula -

=(SUMIF(INDIRECT("C2:C"&L7+1),"Monday",INDIRECT("F2:F"&L7+1))
+SUMIF(INDIRECT("C2:C"&L7+1),"Tuesday",INDIRECT("F2:F"&L7+1))
+SUMIF(INDIRECT("C2:C"&L7+1),"Wednesday",INDIRECT("F2:F"&L7+1))
+SUMIF(INDIRECT("C2:C"&L7+1),"Thursday",INDIRECT("F2:F"&L7+1))
+SUMIF(INDIRECT("C2:C"&L7+1),"Friday",INDIRECT("F2:F"&L7+1)))
/(L7-L8)

The L7 in the indirect statements is a cell where I had =Count(F:F) to
eliminate all but populated cells and since I have a header row, I added 1.
L8 is =COUNTIF(C:C,"Saturday")+COUNTIF(C:C,"Sunday"), thus divide only by the
number of working days (L7-L8).

This isn't elegant, but it seems to be close to the right answer. That is
if the =Sumif() adds time values properly.

I hope you will comment on how I can clean this up.

Thanks,

Charlie

 

[Guest]

Pete,

I wound up using this formula -

=(SUMIF(INDIRECT("C2:C"&L7+1),"Monday",INDIRECT("F2:F"&L7+1))
+SUMIF(INDIRECT("C2:C"&L7+1),"Tuesday",INDIRECT("F2:F"&L7+1))
+SUMIF(INDIRECT("C2:C"&L7+1),"Wednesday",INDIRECT("F2:F"&L7+1))
+SUMIF(INDIRECT("C2:C"&L7+1),"Thursday",INDIRECT("F2:F"&L7+1))
+SUMIF(INDIRECT("C2:C"&L7+1),"Friday",INDIRECT("F2:F"&L7+1)))
/(L7-L8)

The L7 in the indirect statements is a cell where I had =Count(F:F) to
eliminate all but populated cells and since I have a header row, I added 1.
L8 is =COUNTIF(C:C,"Saturday")+COUNTIF(C:C,"Sunday"), thus divide only by the
number of working days (L7-L8).

This isn't elegant, but it seems to be close to the right answer. That is
if the =Sumif() adds time values properly.

I hope you will comment on how I can clean this up.

Thanks,

Charlie