Cellwise Start free

Excel guide · 5 min read

How to calculate average inventory in Excel

Quick answerThe quick version is (beginning inventory + ending inventory) / 2: =(B2+B14)/2. The more accurate version averages every month-end balance, including last year's closing one, so 13 numbers for a year: =AVERAGE(B2:B14). For this shop the two-point average is 64,000 but the 13-point average is 80,615, because stock builds up through the autumn and both year ends fall in the post-holiday low.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1Month endInventory at costCOGS 2026540000Quarter endSKUUnits on handSKUSnow boots
2Dec 31, 202562000Dec 31, 2025Snow boots40
3Jan 31, 202658000Dec 31, 2025Rain jacket85
4Feb 28, 202664000Dec 31, 2025Canvas sneakers120
5Mar 31, 202671000Mar 31, 2026Snow boots25
6Apr 30, 202674000Mar 31, 2026Rain jacket140
7May 31, 202672000Mar 31, 2026Canvas sneakers210
8Jun 30, 202669000Jun 30, 2026Snow boots10
9Jul 31, 202676000Jun 30, 2026Rain jacket60
10Aug 31, 202688000Jun 30, 2026Canvas sneakers260
11Sep 30, 2026104000Sep 30, 2026Snow boots310
12Oct 31, 2026126000Sep 30, 2026Rain jacket150
13Nov 30, 2026118000Sep 30, 2026Canvas sneakers90
14Dec 31, 202666000Dec 31, 2026Snow boots45
15Dec 31, 2026Rain jacket80
16Dec 31, 2026Canvas sneakers115

Download this sheet as an Excel practice file (.xlsx)

Which method to pick

Average inventory feeds inventory turnover, days of inventory, GMROI and carrying cost, so an average that's off by 20% makes all of them off by 20%. Pick the method by how much your stock moves during the period:

  • Stock fairly steady all year: (beginning + ending) / 2 is fine and needs only two counts.
  • Stock that builds for a season or a big launch: average the month-end balances, 13 of them for a year.
  • Average for one SKU, category or warehouse from a snapshot table: AVERAGEIFS on the item column.
  • Average for part of the year, such as the second half: AVERAGEIFS with a date range.

Method 1: beginning plus ending, divided by 2

A2:B14 holds month-end inventory at cost: December 31 last year in row 2, then each month end of this year down to December 31 in row 14. The two-point average uses just the first and last rows.

Use cost, not retail, if the result feeds turnover with cost of goods sold. Mixing a retail-value inventory with a cost-value COGS gives a turnover that looks far too low.

=(B2+B14)/2

Result: 64000

64,000: the average of 62,000 on December 31, 2025 and 66,000 on December 31, 2026.

Method 2: the average of 13 month ends

Average all 13 balances. Why 13 and not 12? Twelve month ends cover January to December, but the year starts with last December's balance. Leaving it out drops the opening stock, and AVERAGE(B3:B14) gives 82,167 instead.

Here the 13-point average is 80,615, about 26% higher than the two-point figure. This shop builds stock from August for the holidays, peaking at 126,000 in October, and sells it down in December. Both year ends land in the low, so the two-point average misses the whole autumn build-up. Any business whose year end falls at a seasonal low, which is often why that date was chosen, has the same problem.

=AVERAGE(B2:B14)

Result: 80615.384615 shows as 80,615 with Number format

80,615: the average of all 13 month-end balances from December 2025 to December 2026.

What if a month-end count is missing?

AVERAGE skips blank cells, so a blank month quietly turns a 13-point average into a 12-point one, weighted towards the months you have. That's acceptable for one gap; for several, fill them from your system's history before averaging. Don't type 0 for a missing month, which drags the average down.

How the choice changes turnover and days of inventory

With 540,000 of cost of goods sold for 2026 in E1, turnover on the two-point average is 8.44 times a year, about 43 days of inventory. On the 13-point average it's 6.70 times, about 54 days. The shop really holds stock for more than a week longer than the simple figure suggests, and anyone comparing it with a competitor would draw the wrong conclusion from the flattering one.

Use the same method every year. A turnover that improves only because you switched from 13 points to 2 isn't an improvement. For the full turnover workflow, see inventory turnover in Excel.

=E1/AVERAGE(B2:B14)

Result: 6.6984732824 shows as 6.70 with Number format

6.70 turns: 540,000 of COGS over 80,615 of average inventory, against 8.44 on the two-point average.

How do I turn that into days of inventory?

Divide 365 by the turnover, or multiply average inventory by 365 and divide by COGS.

=365/(E1/AVERAGE(B2:B14))

Result: 54.49002849 shows as 54.5 with Number format

About 54.5 days on the 13-point average, against 43.3 days on the two-point average.

Average inventory per SKU with AVERAGEIFS

Many systems export stock as a snapshot table: one row per item per count date. G:I has quarter-end units for three SKUs. To average one SKU, put its name in L1 and use AVERAGEIFS on the SKU column. Snow boots average 86 units across the five quarter ends.

The two-point average would say 42.5 (40 at the start, 45 at the end), less than half. The boots spend most of the year near zero and then jump to 310 in September before the winter. That's exactly the kind of item where the simple average hides the cash you tie up. For a list of every SKU, put the names in a column with =UNIQUE(H2:H16) and point AVERAGEIFS at each one.

=AVERAGEIFS(I2:I16,H2:H16,L1)

Result: 86

86 units: the average of 40, 25, 10, 310 and 45 for Snow boots.

Average inventory for part of the year

For a half year or a quarter, add date conditions. The second half of 2026 runs from the June 30 balance (the opening stock for July) to December 31, seven month ends. Build the dates with DATE so they match the real dates in column A; a date typed inside the quotes can be read differently on a computer with another date setting.

The second-half average is 92,429, well above the full-year 80,615, as you'd expect from the autumn build-up. Divide by that half year's COGS, not the full year's, when you work out turnover for the half.

=AVERAGEIFS(B2:B14,A2:A14,">="&DATE(2026,6,30),A2:A14,"<="&DATE(2026,12,31))

Result: 92428.571429 shows as 92,429 with Number format

92,429: the average of the seven month-end balances from June 30 to December 31, 2026.

Common mistakes

Averaging 12 month ends and forgetting the opening balance

A year of month ends is 13 numbers: last year's closing balance plus this year's 12.

Using the two-point average for seasonal stock

If the year end is a seasonal low, the two-point average understates stock. Average monthly counts instead.

Mixing cost and retail values

Value inventory at cost when it feeds turnover with COGS, or at retail for retail-method plans, but never mix the two.

Typing 0 for a missing count

Leave it blank so AVERAGE skips it, or fill it from system history. A zero drags the average down.

FAQ

What is the formula for average inventory?

(Beginning inventory + ending inventory) / 2 for the quick version, or the average of all month-end balances for the period, including the opening one, for the accurate version.

Why use 13 months for average inventory?

A year has 12 month ends, but it starts at last year's closing balance. Including it gives 13 numbers and covers the full year from start to finish.

How do I calculate average inventory for each product?

From a table with one row per product per count date, use =AVERAGEIFS(units,product_column,product_name). Use UNIQUE to list every product and fill the formula down.

Should average inventory be at cost or retail?

At cost when you use it with cost of goods sold, as in turnover and GMROI. Retail-method plans keep everything at retail. Use one basis throughout.

Functions used

Related guides

Updated