Calculate provisioning rate

grietjie

New Member
Joined
Feb 6, 2021
Messages
2
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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!
Male provisioning rate.jpg
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,845
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Aug 12, 2009
Messages
1,691
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
Joined
Feb 6, 2021
Messages
2
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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
Joined
Aug 12, 2009
Messages
1,691
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,258
Messages
5,641,155
Members
417,195
Latest member
Vishal kumar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top