Frequency Formula within a date range

Discussion in 'Business Applications' started by timmycl_7, Nov 19, 2008.

Not open for further replies.
1. Hi,

I have the formula below which counts the frequency at which the word "absent" occurs within the range B2:B366. If it occurs greater than or equal to 28 it displays a 1.

=SUM(IF(FREQUENCY(IF(Sheet1!B2:B366="Absent",ROW(Sheet1!B2:B366)),IF(Sheet1!B2:B366="PRESENT",ROW(Sheet1!B2:B366)))>=28,1))

Is there anyway to adapt the formula to count the frequency within a 12 week period (keeping the same cell range) whereby the first occurrence triggers the count of the 12 week period?

Any help would be appreciated!

Thanks

2. Can you break that down in terms of why it's necessary to have anything more complicated than:

=IF(COUNTIF(B:B,"absent")>27,1,0)

?

3. As to this bit, I'm thrown by your reference to "first occurrence". Nevertheless, here's what I did.

1. Entered dates in A2:A366 ; 6th Nov 08 to 5th Nov 09 inclusive

2. Entered random values in B2:B366 ("absent" or "present")

3. Entered today's date (19th Nov 08) in D1.

Thus:

=SUMPRODUCT((A2:A366>=D1)*(A2:A366<=D1+83)*(B2:B366="Absent"))

gives you the number of "Absent"s from 19th Nov 08 for 12 weeks. HTH 