As my entry to the Egg-Bot Challenge, this Instructable is to show you how to track the laying of eggs for chickens in a simple Excel document. Our family has 6 hens for egg laying, and this March, I decided to track and count the laying of eggs to see if there was any patterns associated with the weather, or other variables as well as just seeing how much they actually lay! Let's get started!
Step 1: What You Will Need
The only thing you will need (other than an egg laying bird) is Microsoft Excel or other spreadsheet program and some basic knowledge on that program.
Step 2: The Document Layout
First off, you will need to know what you want to track. I will be tracking:
1. The number of eggs per day
2. The number of broken eggs from the hens or myself
3. The average collected per day
4. The average 18 egg carton weight
5. The days collected
6. The daily high and low temperature
7. The precipitation daily and monthly
8. The Monthly temperature maximum and minimum
9. The weather conditions
10. And the monthly high and low temperature average
It's a lot to handle every day, but you can customize how you want it.
Start by opening Excel and typing where you want everything and what you want. You can customize the borders, line colors, text colors, fill colors, font, etc.
TIP: Instead of typing in the day for every single cell, just type the first three dates in the first three cells you want, then highlight those three days, and then at the bottom right corner of the cell, if you hold the cursor over it, you will see a solid black cross. Hold down your mouse and drag that all the way down to wherever you want the date range to stop. It will "auto fill" in the days for you. For me, it will stop at the end of each month.
Step 3: Calculations
This is where the data comes in. If you want averages, sums, maximums, minimums, etc, this is how you do it.
TIP: To see if you are entering everything correctly, hold down the CTRL key at the same time as the ~ key to see formulas in their cells and hold down again to hide.
Average: calculates the average of a set of numbers "=AVERAGE(Cell:Cell)"
Sum: totals everything up in a set of numbers "=SUM(Cell:Cell)"
Max: finds the highest number in a set "=MAX(Cell:Cell)"
Min: finds the lowest number in a set "=MIN(Cell:Cell)"
First thing I will go through is how to sum up all the eggs. First, go to the cell you would like to see the sum in. This is simple, just type "=SUM(B4:B34)" and press enter (B4:B34 may be different for you. The colon : means through, so B4 through B34 is where it sums the data up) and that's it. Now every time you collect eggs, and enter it in the proper spot, you will get the sum from previous days and that day.
For this, go to the cell you want the average in, then type "=AVERAGE(B4:B34)" (the number range needs to be the same as before, otherwise your numbers won't be accurate) Now, this will give you the average number of eggs per day.
If you want to know how many days you have collected eggs, this is how: type "=COUNT(B4:B34)" into the cell you want to know the days collected.
This will let you view how many eggs have been broken by you or your hens. Just follow the steps from "Egg Totals" except, change the cell range to where you have the broken eggs.
This is completely up to you. There isn't a formula I am aware of to automatically update the weather in an Excel document, so this part is easiest to do at the end of each month. Go to: AccuWeather.com , hold your cursor over Forecast, when the drop-down menu appears, hold the cursor over month outlook and click on list view. Another way to do it, is type in your city and state, and under the month forecast, find the list view. This will give you forecasts on the precipitation, temperature, and weather. This also acts as an archive for you to see the whole month's totals.
If you want the Max temperature to display in your excel document, type "=MAX(D4:D34)" or wherever you have your high temperature range.
If you want the Minimum temperature to display in your excel document, type "=MIN(E4:E34)" or wherever you have your low temperature range.
Average Low Temperature
If you want the average monthly low temperature, use the average formula for your cell range of low temperature range. For me it is "=AVERAGE(E4:E34)"
Average High Temperature
If you want the average monthly high temperature, simply use the average formula for the range of your high temperature range. For me it is "=AVERAGE(D4:D34)"
Another easy one, if you want to see how much rain or snow you have received, just use the sum formula for the range of numbers as the precipitation.
If you decide to see how much the average 12 or 18 egg carton weighs, just make a list of all the full carton weights you get, and using the average formula, you can easily find the average weight. I only did this for one month, because it ends up being fairly difficult keeping track of which eggs have already been weighed and the ones that haven't.
One thing you can also do is something called "Conditional Formatting." What this does is, say you want to highlight every time you collect more than 4 eggs or see what temperatures drop below 32. This can be very useful if you don't want to put everything in manually. First step is to highlight the cells you want to
Step 4: Wait and See!
Now, all you have to do is fill in the data. For the month of March, this is what I got:
Total Eggs from the 6 hens:
Total Broken Eggs :
Average Number per Day:
Monthly Low Temperature Average
Monthly High Temperature Average
Monthly Minimum Temperature
Monthly Maximum Temperature
Average 18 Egg Carton Weight
I have already started tracking April, but I am not tracking the average carton weight anymore. There didn't appear to be a specific pattern, but we usually never get 6 eggs (only 3 times in March), partially due to a Hen having had infectious bronchitis (at least that's what we think) but we don't know for sure.
Step 5: Finished
Now you are done with the Excel document. All you have to do is keep filling in the numbers, graph it if you want, and watch the numbers. It will be fairly interesting at the end of the year seeing all of it put together and the real numbers. I hope you enjoyed this Excel walk-through on how to track your hen's egg laying. Please Vote for my Instructable in the Egg-Bot Challenge and leave your comments!