Hi all,
I hope you're not tired of me just yet.
I'm attaching an Excel sheet of a problem I'm trying to solve. I have two columns and I want to do a what-if analysis/conditional query based on column 1. What I have is a set of time data ranging from 09:30 to 16:00. I have set this up as intervals with 09:30-10:00, 10-11:00, ..., 15-16:00.
So, in F5, I want to check how many times when the LOD is between 09:30 and 10:00 the same is true for the HOD:
LOD <= 10:00 + HOD <= 10:00
I checked manually and this should be N = 5, I think.
In G5 I want to check how many times the HOD is between 10-11:00 when the LOD is between 09:30-10:00:
LOD <= 10:00 + HOD > 10:00 & <= 11:00
Manual check: N = 33.
I can see a very cumbersome way to do this by creating helper columns for all these different counts. But I can't seem to set up a formula that counts if A = X & B = Y for the entire set of data.
Any ideas?
Thanks in advance for any pointers.
PS: Not sure why my count didn't add up (809 vs 800).
Bookmarks