When Averaging a column, exclude value based on another cell v
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
|