1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Frequency Formula within a date range

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

Thread Status:
Not open for further replies.
  1. timmycl_7

    timmycl_7 Thread Starter

    Joined:
    Nov 28, 2007
    Messages:
    50
    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. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    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. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    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 :)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/770789

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice