# Calculate provisioning rate

#### grietjie

##### New Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

##### Well-known Member
Welcome to MrExcel Message Board.
For Result Between 7 AM & 8 AM :
Excel Formula:
``=COUNTIFS(\$J\$2:\$J\$100,">=" & 7/24,\$J\$2:\$J\$100,"<"  & 8/24)``

#### Misca

##### Well-known Member
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:
Excel Formula:
``````=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

##### New Member
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

##### Well-known Member
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:
Excel 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
Excel Formula:
``=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.

Replies
12
Views
145
Replies
0
Views
149
Replies
2
Views
165
Replies
2
Views
394
Replies
1
Views
45

1,126,986
Messages
5,622,002
Members
415,873
Latest member
fuulhouse

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back