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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Divercem - 18 Aug 2006 16:53 GMT

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

Reply to this Message

Pete_UK - 18 Aug 2006 17:24 GMT

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

> 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
[quoted text clipped - 7 lines]
>
> Charlie

Reply to this Message

Divercem - 18 Aug 2006 18:32 GMT

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?

> Try this:
>
[quoted text clipped - 22 lines]
> >
> > Charlie

Reply to this Message

Pete_UK - 18 Aug 2006 21:42 GMT

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

> 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
[quoted text clipped - 31 lines]
> > >
> > > Charlie

Reply to this Message

Divercem - 18 Aug 2006 22:09 GMT

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!

> Presumably column references won't work - try amending it as follows:
>
[quoted text clipped - 41 lines]
> > > >
> > > > Charlie

Reply to this Message

Divercem - 21 Aug 2006 23:33 GMT

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

> Presumably column references won't work - try amending it as follows:
>
[quoted text clipped - 41 lines]
> > > >
> > > > Charlie

Reply to this Message