+ Reply to Thread
Results 1 to 4 of 4

Count different entries for time intervals

  1. #1
    Registered User
    Join Date
    01-17-2019
    Location
    Germany
    MS-Off Ver
    varying
    Posts
    19

    Question Count different entries for time intervals

    Hello,
    i need to evaluate a paleo diversity database in RStudio but to be able to do the i need to "clean up" the database first. I do not have much excel experience yet so please excuse my probably mundane questions/requests.

    I have already recieved alot of help regarding the workup of the database. Thanks again to all contributors.

    Im now facing a new excel related hurdle. Ill provide an exemplary part of the database im working with and will refer to it.

    I need to count the number of different values in column "A" for a range of values in column "I". It would be even better if it would check 2 columns ("G" and "H"). So if one or both values from column "G" and "H" fit into an defineable interval the are considered for the count of dierent values from column "A".
    Here is an example of what i want to do:
    I want to count the number of different genera over a time period. For example the number of different "A" values (genera) for "I" values in the interval <358,9 and >298,9. (preferably it would be checking columns "G" and "H" and counts the number of different "A" values even if just one of the ages fall into the defined interval) So i can create a chart of the number of different genera for all timeperiods.

    Or is there any way to employ Power Query for my purpose?

    Edit: I've found this function =if(or(and(H1>=0,H1<=10),and(0>=H1,0<=G1))
    which was derived from this: =if(or(and(x>=a,x<=b),and(a>=x,a<=y)),"Overlap","Do not overlap")
    can someone add the necessary arguments so it puts out the value for "A" in the corresponding row (maybe like this : =if(or(and(H2>=0,H2<=10),and(0>=H1,0<=G2)),A2,"0") ive tried this formula and it returns an error which is #NAME? in german excel


    Thanks in advance
    afridelle
    Attached Files Attached Files
    Last edited by afridelle; 01-24-2019 at 10:28 AM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,164

    Re: Count different entries for time intervals

    I am not sure I have the concept(s). Please check the attached.

    For the column I comparisons I have copied column A, pasted into column P and removed duplicates. The <358,9 and >298,9 criteria are in Q1:R1. Then this formula in Q2 filled down and across. There are numbers > 0 returned in column Q but not in column R.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In columns T:V the setup is the same. Attempted to do comparisons for G:H respectively. I suspect this one is wrong, but please have a look and give me guidance. In U2 filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Registered User
    Join Date
    01-17-2019
    Location
    Germany
    MS-Off Ver
    varying
    Posts
    19

    Re: Count different entries for time intervals

    Thanks
    the function is not excatly doing what i want it to do this is probably due to my insufficient explaination.
    sorry for that i was in a hurry when i posted this yesterday.
    i've thought of a more general way to express what im looking for. it basically needs a translation to excel speak :p

    count number of dissimilar values/items in column A if interval overlap#counting the amount of different items/values (in this case genera)in column A
    interval1 [n*x;(n+1)*x] #this is the range which should be considered for counting; x is the interval size (e.g. 10 million years); n= 0,1,2,3...; so for x = 10 and n=0 it's [0*10;(0+1)*10] -> [0;10]; n=1 ->[10;20] and so on
    interval2 [G;H] #this is the timeinterval for which a specific genus is known; it should grab the values for the interval from the G and H column
    return "n" and number of dissimilar values for n

    n= #of genera
    0 20
    1 17
    2 23

    i hope this helps in explaining and somebody could write this into an excle compatible code

    thanks in advance
    afridelle

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,613

    Re: Count different entries for time intervals

    I feel as if this will also not be quite what you are looking for, but perhaps bring you a step closer.
    As Dave described I put a list of unique genera in column O using the advanced filter feature.
    Column P gives the minimum time frame for the genus using: =MINIFS(H$2:H$400,A$2:A$400,O2)
    Column Q gives the maximum time frame for the genus using: =MAXIFS(G$2:G$400,A$2:A$400,O2)
    Note that MINIFS and MAXIFS are available in the 2019 version (please update the profile so we can customize formulas to the version(s) you use)
    Column U:W just give the n, n*x and (n+1)*x values
    Column X is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If this isn't what you want I would suggest uploading another file that:
    1. Is smaller,
    2. Shows how you want the genera counted (for example there are 19 instances of Tetradium however it would appear that the members of the genera listed in row 8 did not coexist with the members listed in rows 9:11, so we need to know if that counts as two)
    3. based on the smaller sample, manually construct an output table, like the one in post #3. We may then be able to write formulas/code that will replicate the numbers in the output table, and you will hopefully be able to apply those formulas/code to your actual data.
    Let us know if you have any questions.
    Attached Files Attached Files
    Last edited by JeteMc; 01-31-2019 at 01:31 AM. Reason: Corrected Error in Formula/File
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 11-26-2014, 10:35 PM
  2. IF statement for count of entries outside of specified time
    By jdstuffel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-03-2014, 11:15 AM
  3. Excel 2000 Count Entries within a time frame.
    By Daniel Salinas in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-10-2014, 12:10 PM
  4. Count of current chats (time intervals)
    By Alex Fate in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2013, 02:22 AM
  5. Converting Data in Irregular Time Intervals into Regular Time Intervals
    By AlexJT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2011, 02:42 AM
  6. Need to Transpose Irregular Time Intervals into Regular Time Intervals
    By AlexJT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2011, 08:30 PM
  7. Count time (entries per hour)
    By canonelan2 in forum Excel General
    Replies: 6
    Last Post: 10-07-2008, 03:23 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1