Count different entries for time intervals

[afridelle]

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

[FlameRetired]

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.

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.

Please Login or Register  to view this content.

[afridelle]

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

[JeteMc]

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:

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.