Calculate provisioning rate

[grietjie]

Hi there!
I'm fairly new to using formulas in excel, so help would greatly be appreciated!
I need to calculate the male provisioning rate per hour (how many times the male visited the nest in an hour). I've tried IF and COUNTIF formulas, but I just can't figure it out.
Thank you in advance!

 

[maabadi]

Welcome to MrExcel Message Board.
For Result Between 7 AM & 8 AM :

=COUNTIFS($J$2:$J$100,">=" & 7/24,$J$2:$J$100,"<"  & 8/24)

 

[Misca]

Welcome to the board!

I think there's two different approaches here: Visits within an hour from any given time period or average visits per hour:

=COUNTIFS(TimeRange,">="&F3,TimeRange,"<"&F3+TIMEVALUE("1:00:00"),VisitsRange,"yes")

=ROUND(COUNTIFS(VisitsRange,"yes")/((MAX(TimeRange)-MIN(TimeRange))*24),2)

The first formula counts the visits during the next 60 minutes from the start time in F3.

The next formula counts the visits and divides them by the difference between the first and last observations. Since Excel handles times as part of a day (one hour = 1/24) I'm multiplying that with 24 to get the hours as whole number and minutes/seconds as the decimal part. I decided to wrap it all inside a ROUND function to see only the first 2 decimals but you can leave that out if you want to have the more exact number.

 

[grietjie]

Thanks! This really helped. I couldn't figure out how to include 'time'.
Is there a way to drag the formula across multiple days and times?
At the moment I am manually changing the hours for each day, which will take a lot of time since my data covers multiple months.

 

[Misca]

At the moment the COUNTIFS-formula is comparing the time. If your time value includes the date as well (Excel date time is basically just a number format where the decimals stand for the hours, minutes and seconds) you should be able to drag the formulas down as it is. If your date is in another column you're going to have to include that into the formula:

=COUNTIFS(TimeRange,">="&B2,TimeRange,"<"&B2+TIMEVALUE("1:00:00"),VisitsRange,"yes",DateRange,A2)

In my example the date can be found in column A and Time in column B. They can be the same as your Date and Time Ranges but they don't have to be: If they are, the formula is counting the visits from that time on but if they are not you can see the visits in the way you want to: If you had the sunrise time in B1 and sunset time in C1 something like

=COUNTIFS(TimeRange,">="&B2,TimeRange,"<"&C2,VisitsRange,"yes",DateRange,A2)

returns the number of visits between the sunrise and sunset on the date you've entered in A2.