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

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: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

Try this:

=AVERAGE(IF((C:C<>"Saturday")*(C:C<>"Sunday"),D: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

Divercem wrote:
> 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: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

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" wrote:

> Try this:
>
> =AVERAGE(IF((C:C<>"Saturday")*(C:C<>"Sunday"),D: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
>
> Divercem wrote:
> > 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: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
>
>

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

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

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

Hope this helps.

Pete

Divercem wrote:
> 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" wrote:
>
> > Try this:
> >
> > =AVERAGE(IF((C:C<>"Saturday")*(C:C<>"Sunday"),D: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
> >
> > Divercem wrote:
> > > 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: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
> >
> >

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: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!

"Pete_UK" wrote:

> Presumably column references won't work - try amending it as follows:
>
> =AVERAGE(IF((C1:C65522<>"Saturday")*(C1:C65522<>"Sunday"),D1:D65522))
>
> This is almost a complete column. Again, CSE to commit the formula.
>
> Hope this helps.
>
> Pete
>
> Divercem wrote:
> > 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" wrote:
> >
> > > Try this:
> > >
> > > =AVERAGE(IF((C:C<>"Saturday")*(C:C<>"Sunday"),D: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
> > >
> > > Divercem wrote:
> > > > 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: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,

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

"Pete_UK" wrote:

> Presumably column references won't work - try amending it as follows:
>
> =AVERAGE(IF((C1:C65522<>"Saturday")*(C1:C65522<>"Sunday"),D1:D65522))
>
> This is almost a complete column. Again, CSE to commit the formula.
>
> Hope this helps.
>
> Pete
>
> Divercem wrote:
> > 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" wrote:
> >
> > > Try this:
> > >
> > > =AVERAGE(IF((C:C<>"Saturday")*(C:C<>"Sunday"),D: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
> > >
> > > Divercem wrote:
> > > > 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: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,

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

"Pete_UK" wrote:

> Presumably column references won't work - try amending it as follows:
>
> =AVERAGE(IF((C1:C65522<>"Saturday")*(C1:C65522<>"Sunday"),D1:D65522))
>
> This is almost a complete column. Again, CSE to commit the formula.
>
> Hope this helps.
>
> Pete
>
> Divercem wrote:
> > 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" wrote:
> >
> > > Try this:
> > >
> > > =AVERAGE(IF((C:C<>"Saturday")*(C:C<>"Sunday"),D: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
> > >
> > > Divercem wrote:
> > > > 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: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
> > >
> > >
>
>