Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Hi. I need to get the average of every 6th cell in a column, excluding errors
--- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Try something like this:
=AVERAGE(IF(MOD(ROW($Z$4:$Z$48),6)=4,IF(ISNUMBER($ Z$4:$Z$48)<"",$Z$4:$A$48))) for row 4 =AVERAGE(IF(MOD(ROW($Z$6:$Z$48),6)=0,IF(ISNUMBER($ Z$6:$Z$48)<"",$Z$6:$A$48))) for row 6 =AVERAGE(IF(MOD(ROW($Z$8:$Z$48),6)=2,IF(ISNUMBER($ Z$8:$Z$48)<"",$Z$8:$A$48))) for row 8 Accept each of these using CTRL SHIFT ENTER I think that should do it. "tamiluchi" wrote: Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Z4 on:
=AVERAGE(IF((MOD(ROW($Z$4:$Z$1000),6)=4)*($Z$4:$Z$ 1000<""),$Z$4:$Z$1000)) Z6 on: =AVERAGE(IF((MOD(ROW($Z$4:$Z$1000),6)=0)*($Z$4:$Z$ 1000<""),$Z$4:$Z$1000)) Z8 on: =AVERAGE(IF((MOD(ROW($Z$4:$Z$1000),6)=2)*($Z$4:$Z$ 1000<""),$Z$4:$Z$1000)) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tamiluchi" wrote in message ... Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Three array formulas, meaning enter them with Ctrl-Shift-Enter
for the first one: =AVERAGE(IF(MOD(ROW(Z4:Z1000)-4,6)=0,Z4:Z1000)) second one =AVERAGE(IF(MOD(ROW(Z6:Z1000)-6,6)=0,Z4:Z1000)) last one =AVERAGE(IF(MOD(ROW(Z8:Z1000)-8,6)=0,Z4:Z1000)) "tamiluchi" wrote: Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Using these formulas, I'm getting the div/0 error in the first 2 averages and
the third average is incorrect. (I figured it manually for a check) "Barb Reinhardt" wrote: Try something like this: =AVERAGE(IF(MOD(ROW($Z$4:$Z$48),6)=4,IF(ISNUMBER($ Z$4:$Z$48)<"",$Z$4:$A$48))) for row 4 =AVERAGE(IF(MOD(ROW($Z$6:$Z$48),6)=0,IF(ISNUMBER($ Z$6:$Z$48)<"",$Z$6:$A$48))) for row 6 =AVERAGE(IF(MOD(ROW($Z$8:$Z$48),6)=2,IF(ISNUMBER($ Z$8:$Z$48)<"",$Z$8:$A$48))) for row 8 Accept each of these using CTRL SHIFT ENTER I think that should do it. "tamiluchi" wrote: Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Try these ARRAY FORMULAS*:
For data in Z4:Z30 Every 6th item beginning with Z4 =AVERAGE(IF(ISNUMBER($Z$4:$Z$30)*(MOD(ROW($Z$4:$Z$ 30)-4,6)=0),$Z$4:$Z$30)) Every 6th item beginning with Z6 =AVERAGE(IF(ISNUMBER($Z$6:$Z$30)*(MOD(ROW($Z$6:$Z$ 30)-6,6)=0),$Z$6:$Z$30)) Every 6th item beginning with Z8 =AVERAGE(IF(ISNUMBER($Z$8:$Z$30)*(MOD(ROW($Z$8:$Z$ 30)-8,6)=0),$Z$8:$Z$30)) *Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter] Is that something you can work with? Does that help? *********** Regards, Ron XL2002, WinXP "tamiluchi" wrote: Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Thanks, but I'm still getting an error. Maybe I should clarify "ignoring
errors". The cells being averaged contain averages themselves. I'm trying to get an overall average in 3 categories. Some of the cells have an error because that particular employee did nothing in that particular category during this time frame. I need the formula to ignore those cells with errors in getting the overall average. Sorry if I wasn't clear on that. "Duke Carey" wrote: Three array formulas, meaning enter them with Ctrl-Shift-Enter for the first one: =AVERAGE(IF(MOD(ROW(Z4:Z1000)-4,6)=0,Z4:Z1000)) second one =AVERAGE(IF(MOD(ROW(Z6:Z1000)-6,6)=0,Z4:Z1000)) last one =AVERAGE(IF(MOD(ROW(Z8:Z1000)-8,6)=0,Z4:Z1000)) "tamiluchi" wrote: Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Forgot to add that these are array formulae, should be committed with
Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Bob Phillips" wrote in message ... Z4 on: =AVERAGE(IF((MOD(ROW($Z$4:$Z$1000),6)=4)*($Z$4:$Z$ 1000<""),$Z$4:$Z$1000)) Z6 on: =AVERAGE(IF((MOD(ROW($Z$4:$Z$1000),6)=0)*($Z$4:$Z$ 1000<""),$Z$4:$Z$1000)) Z8 on: =AVERAGE(IF((MOD(ROW($Z$4:$Z$1000),6)=2)*($Z$4:$Z$ 1000<""),$Z$4:$Z$1000)) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tamiluchi" wrote in message ... Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Fabulous!! That worked perfectly. Thank you so very much.
Tammie "Ron Coderre" wrote: Try these ARRAY FORMULAS*: For data in Z4:Z30 Every 6th item beginning with Z4 =AVERAGE(IF(ISNUMBER($Z$4:$Z$30)*(MOD(ROW($Z$4:$Z$ 30)-4,6)=0),$Z$4:$Z$30)) Every 6th item beginning with Z6 =AVERAGE(IF(ISNUMBER($Z$6:$Z$30)*(MOD(ROW($Z$6:$Z$ 30)-6,6)=0),$Z$6:$Z$30)) Every 6th item beginning with Z8 =AVERAGE(IF(ISNUMBER($Z$8:$Z$30)*(MOD(ROW($Z$8:$Z$ 30)-8,6)=0),$Z$8:$Z$30)) *Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter] Is that something you can work with? Does that help? *********** Regards, Ron XL2002, WinXP "tamiluchi" wrote: Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average every 6th cell in a column
Thanks for the feedback, Tammie....I'm glad that worked for you.
*********** Regards, Ron XL2002, WinXP "tamiluchi" wrote: Fabulous!! That worked perfectly. Thank you so very much. Tammie "Ron Coderre" wrote: Try these ARRAY FORMULAS*: For data in Z4:Z30 Every 6th item beginning with Z4 =AVERAGE(IF(ISNUMBER($Z$4:$Z$30)*(MOD(ROW($Z$4:$Z$ 30)-4,6)=0),$Z$4:$Z$30)) Every 6th item beginning with Z6 =AVERAGE(IF(ISNUMBER($Z$6:$Z$30)*(MOD(ROW($Z$6:$Z$ 30)-6,6)=0),$Z$6:$Z$30)) Every 6th item beginning with Z8 =AVERAGE(IF(ISNUMBER($Z$8:$Z$30)*(MOD(ROW($Z$8:$Z$ 30)-8,6)=0),$Z$8:$Z$30)) *Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter] Is that something you can work with? Does that help? *********** Regards, Ron XL2002, WinXP "tamiluchi" wrote: Hi. I need to get the average of every 6th cell in a column, excluding errors --- three times. The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8. Is there an easy way to achieve these 3 averages? Tammie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
run a macro in a locked cell | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
up to 7 functions? | Excel Worksheet Functions | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions |