Countif Until

[aoifew]

Hi
I’d like to average hours worked every 5 days but need toexclude days where no hours have been worked. Was playing about with AverageIf to achieve this and wouldn’t be aproblem for the formula in cell below (answer is 9.6). However, if I drag the formula along (Formula 2), the average will onlybe for 4 days as it’ll ignore the zero in the cell for Day 6. Is there a way of dynamically referencing theprevious 5 cells where hours have been worked?

Thanks

[TABLE="width: 436"]
<tbody>[TR]
[TD="width: 53, bgcolor: transparent"]Day 1
[/TD]
[TD="width: 40, bgcolor: transparent"]Day 2
[/TD]
[TD="width: 40, bgcolor: transparent"]Day 3
[/TD]
[TD="width: 40, bgcolor: transparent"]Day 4
[/TD]
[TD="width: 68, bgcolor: transparent"]Day 5
[/TD]
[TD="width: 68, bgcolor: transparent"] Day 6
[/TD]
[TD="width: 68, bgcolor: transparent"]Day 7
[/TD]
[TD="width: 68, bgcolor: transparent"]Day 8
[/TD]
[TD="width: 68, bgcolor: transparent"]Day 9
[/TD]
[TD="width: 68, bgcolor: transparent"]Day 10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]9.6
[/TD]
[TD="bgcolor: transparent"]Formula 2
[/TD]
[TD="bgcolor: transparent"]Formula 3
[/TD]
[TD="bgcolor: transparent"]Formula 4
[/TD]
[TD="bgcolor: transparent"]Formula 5
[/TD]
[TD="bgcolor: transparent"]Formula 6

[/TD]
[/TR]
</tbody>[/TABLE]

 

[kweaver]

In your example, does the 2nd result average day2 through day6 but not consider the 0 in day6? So, the result would be 10?

 

[Nishant94]

Is this what you want:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Day 9	Day 10
2	8	8	8	12	12	0	0	8	8	8
3	Average->				9.6	10	10.66667	10.66667	9.333333	8
Sheet8

Cell Formulas
Range	Formula
E3	=AVERAGEIF(A2:E2,">0")

 

[aoifew]

Hi
Thanks for the responses and apologies for my less than clear explanation.

In the example given, as the hours worked on Day 6 was zero, I'd like the formula in F3 to ignore zeroes and simply reflect the last 5 days worked. i.e. 9.6. Same will apply to G3 but on Day 8 the answer should be the average of Days 8,5,4,3,and 2 (previous 5 days that "<> 0") which coincidentally in my poor example is also 9.6

 

[Nishant94]

Is this what you are looking for:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Day 9	Day 10
2	8	8	8	12	12	0	0	8	8	8
3	Average->				9.6	9.6	9.6	9.6	9.6	9.6
Sheet8

Cell Formulas
Range	Formula
E3	=AVERAGEIF(INDEX($A2:E2,COLUMNS($A3:E3)-COUNTIF($A2:E2,"0")-4):E2,">0")

 

[aoifew]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Day1
[/TD]
[TD="align: center"]Day2
[/TD]
[TD="align: center"]Day 3
[/TD]
[TD="align: center"]D4
[/TD]
[TD="align: center"]D5
[/TD]
[TD="align: center"]D6
[/TD]
[TD="align: center"]D7
[/TD]
[TD="align: center"]D8
[/TD]
[TD="align: center"]D9
[/TD]
[TD="align: center"]D10
[/TD]
[TD="align: center"]D11
[/TD]
[TD="align: center"]D12
[/TD]
[TD="align: center"]D13
[/TD]
[TD="align: center"]D14
[/TD]
[TD="align: center"]D15
[/TD]
[TD="align: center"]D16
[/TD]
[TD="align: center"]D17
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9.2
[/TD]
[TD="align: center"]9.2
[/TD]
[TD="align: center"]9.2
[/TD]
[TD="align: center"]9.8
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]9.8
[/TD]
[TD="align: center"]9.8
[/TD]
[TD="align: center"]9.4
[/TD]
[TD="align: center"]9.4
[/TD]
[TD="align: center"]9.4
[/TD]
[TD="align: center"]9.166
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"]9.28571
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9.8
[/TD]
[TD="align: center"]9.8
[/TD]
[TD="align: center"]9
[/TD]
[/TR]
</tbody>[/TABLE]
This appeared to work but when extending it over a much wider range it started to deviate way from the average (issue starts at Day 15, I've inserted what the value should be in row 4 from that point). Please note, I change the daily values to give more varying results

Thanks

 

[Nishant94]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Day 9	Day 10	Day 11	Day 12	Day 13	Day 14	Day 15	Day 16	Day 17
2	3	7	13	12	1	0	0	6	8	12	12	9	0	0	8	8	8
3	Average->				7.2	7.2	7.2	7.8	8	7.8	7.8	9.4	9.4	9.4	9.8	9.8	9
Sheet9

Cell Formulas
Range	Formula
E3	=SUMPRODUCT(($A2:E2)*(ISNUMBER(MATCH(COLUMN($A3:E3)-COLUMN($A3)+1,AGGREGATE(14,6,(COLUMN($A3:E3)-COLUMN($A3)+1)^(($A2:E2)^0),{5,4,3,2,1}),0))))/5

 

[aoifew]

Apologies Nishant for not getting back sooner and thank you for your time.

I attempted to decipher your formula for some considerable time after you posted it and I still haven't got a grasp but it does work, well impressed!

Would it be possible to revise the formula so that it can average over significantly longer periods of time? For example, 120 days or even 365 days rather than just 5 days?

 

[aoifew]

Hi (and apologies Fluff for the duplicate)

Nishant94 has very kindly pulled the following formula together for me (an awesome feat in itself) and I was wondering if someone could assist taking it to the next level.

Would it be possible to revise the formula below so that it can average over significantly longer periods of time? For example, 120 days or even 365 days rather than just 5 days?


[TABLE="class: cms_table"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[TH="align: center"]H[/TH]
[TH="align: center"]I[/TH]
[TH="align: center"]J[/TH]
[TH="align: center"]K[/TH]
[TH="align: center"]L[/TH]
[TH="align: center"]M[/TH]
[TH="align: center"]N[/TH]
[TH="align: center"]O[/TH]
[TH="align: center"]P[/TH]
[TH="align: center"]Q[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Day 1[/TD]
[TD="align: center"]Day 2[/TD]
[TD="align: center"]Day 3[/TD]
[TD="align: center"]Day 4[/TD]
[TD="align: center"]Day 5[/TD]
[TD="align: center"]Day 6[/TD]
[TD="align: center"]Day 7[/TD]
[TD="align: center"]Day 8[/TD]
[TD="align: center"]Day 9[/TD]
[TD="align: center"]Day 10[/TD]
[TD="align: center"]Day 11[/TD]
[TD="align: center"]Day 12[/TD]
[TD="align: center"]Day 13[/TD]
[TD="align: center"]Day 14[/TD]
[TD="align: center"]Day 15[/TD]
[TD="align: center"]Day 16[/TD]
[TD="align: center"]Day 17[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Average->[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7.2[/TD]
[TD="align: right"]7.2[/TD]
[TD="align: right"]7.2[/TD]
[TD="align: right"]7.8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7.8[/TD]
[TD="align: right"]7.8[/TD]
[TD="align: right"]9.4[/TD]
[TD="align: right"]9.4[/TD]
[TD="align: right"]9.4[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]


​

Worksheet Formulas

[TABLE="class: cms_table, width: 100%"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TH="width: 10, align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, align: center"]E3[/TH]
[TD="align: left"]=SUMPRODUCT(($A2:E2)*(ISNUMBER(MATCH(COLUMN($A3:E3)-COLUMN($A3)+1,AGGREGATE(14,6,(COLUMN($A3:E3)-COLUMN($A3)+1)^(($A2:E2)^0),{5,4,3,2,1}),0))))/5[/TD]
[/TR]
</tbody>[/TABLE]
​