Apiary: Winter Feed Shortfall And Sugar Bags Per Yard

Excel Formulas › Beekeeping & Apiary

All versions

Every fall the question is whether the bees have enough to get to spring. Each hive needs a certain weight of stores for your winter; the yard has a certain weight on the scale now. The gap is what you feed. MAX keeps a well-stocked yard from showing a negative shortfall, and ROUNDUP turns pounds into the whole sugar bags you actually buy.

Quick formula: Hives times pounds needed per hive, minus stores on hand, never below zero:

=MAX(0,B2*C2-D2)

Six hives that each need 60 lb is 360 lb; the yard weighs in at 280 lb of stores, so the shortfall is 80 lb — two 50 lb bags of sugar.

Functions used (tap for the full reference guide):

MAX ROUNDUP

The example

Three yards. The big yard is already over target; the northern out-yard needs a heavier winter target and is well short.

	A	B	C	D	E	F
1	Yard	Hives	Lb per hive	Stores on hand	Shortfall lb	Sugar bags
2	Home yard	6	60	280	80	2
3	River bottom	12	70	900	0	0
4	North out-yard	3	90	150	120	3

The formula

Shortfall first, then bags:

=MAX(0,B2*C2-D2) then =ROUNDUP(E2/50,0) // pounds short, then 50 lb bags

How it works

Three small decisions, each hidden in one function:

B2*C2 is the target: 6 hives that each need 60 lb is 360 lb of stores for the yard.
-D2 subtracts what is already in the hives, from a fall weigh-in or a heft estimate. 360 minus 280 is 80 lb short.
MAX(0,...) floors it. The river-bottom yard is 60 lb over target; without MAX it would show −60 and the bag count would go negative.
ROUNDUP(E2/50,0) converts to whole bags at roughly a pound of sugar per pound of stores. 80 lb is 1.6 bags — buy 2.

The one-pound-per-pound rule of thumb assumes 2:1 syrup, which the bees dry down to close to its sugar weight. If you feed 1:1 in early fall, expect to use more sugar per pound of finished stores.

Try it: interactive demo

Interactive

Enter hive count, the winter target per hive, stores on hand and the bag size you buy.

Hives

Lb needed per hive

Stores on hand (lb)

Bag size (lb)

Variations

Per-hive shortfall list

If you weigh each hive, run the MAX per row and SUM the column so the light hives get the feed, not the yard average.

=SUM(MAX(0,Target-Hive1),MAX(0,Target-Hive2),...) or =SUMPRODUCT((Target-Weights>0)*(Target-Weights))

Syrup gallons instead of bags

A gallon of 2:1 syrup carries about 8 lb of sugar. Divide the shortfall by 8 for gallons to mix.

=ROUNDUP(E2/8,1)

Pitfalls & errors

"Stores on hand" means honey and syrup, not gross hive weight. Subtract the woodenware, bees and pollen first, or the yard will look far better stocked than it is.

Pounds per hive is a regional number. Southern yards may winter on 40 lb; northern double-deeps may need 90 or more. Use your own losses from last spring to set it.

Do not average a strong yard against a weak one. MAX on the yard total hides three light hives next to three heavy ones — use the per-hive variation when you have hive-level weights.

Practice workbook

📊

Download the free Apiary: Winter Feed Shortfall And Sugar Bags Per Yard practice workbook

Edit the yellow hive, target and stores cells; shortfall and sugar bags recalculate.

Frequently asked questions

Why MAX(0, ...) instead of IF?

IF(B2*C2-D2<0,0,B2*C2-D2) works too, but it repeats the calculation. MAX(0, x) says the same thing once and is the standard way to floor a value at zero.

Is one pound of sugar really one pound of stores?

Close enough for buying sugar. Bees dry 2:1 syrup down to roughly 80–83% sugars, so a pound of dry sugar becomes a little more than a pound of capped stores. Rounding up to whole bags covers the difference.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: MAX ROUNDUP