Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When Averaging a column, exclude value based on another cell value
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When Averaging a column, exclude value based on another cell v
Presumably column references won't work - try amending it as follows:
=AVERAGE(IF((C1:C65522<"Saturday")*(C1:C65522<"S unday"),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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When Averaging a column, exclude value based on another cell v
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<"S unday"),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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When Averaging a column, exclude value based on another cell v
Pete,
I wound up using this formula - =(SUMIF(INDIRECT("C2:C"&L7+1),"Monday",INDIRECT("F 2:F"&L7+1)) +SUMIF(INDIRECT("C2:C"&L7+1),"Tuesday",INDIRECT("F 2: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<"S unday"),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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions |