Cellwise Start free

Excel guide · 4 min read

How to calculate days of inventory on hand in Excel

Quick answerDays of inventory on hand for an item is units on hand divided by average daily usage. Get usage from your issues log with SUMIFS over the last 90 days and divide by 90, then =F2/I2. Wrap it as =IFERROR(F2/I2,"No usage") so items that haven't moved don't show #DIV/0!.

Sample data

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

RowABCDEFGHIJKL
1Issue dateSKUQty issuedSKUOn handLead time (days)Usage in windowAvg daily usageDays on handFlag
2Jul 20, 2026GKT-9040VAL-125214360413Reorder
3Aug 18, 2026VAL-12120HOS-30210211351.5140OK
4Aug 30, 2026HOS-3045FLT-05180109001018OK
5Sep 9, 2026FLT-05300GKT-90753000No usageDead stock
6Sep 22, 2026VAL-12150
7Oct 3, 2026HOS-3060
8Oct 14, 2026FLT-05280
9Oct 20, 2026VAL-1290
10Oct 31, 2026FLT-05320
11Nov 6, 2026HOS-3030

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

Average daily usage from the issues log

You need two things per SKU: what's on the shelf now (on hand, column F) and how fast it leaves. The second comes from an issues or sales log with a date, a SKU and a quantity (A:C in the sample).

Put the report date (N1) and the window length in days (N2, 90) in input cells. Usage in the window, in H2: =SUMIFS($C$2:$C$11,$B$2:$B$11,E2,$A$2:$A$11,">"&$N$1-$N$2,$A$2:$A$11,"<="&$N$1). That's every issue of the SKU after 12 August and up to the report date. Average daily usage in I2 is =H2/$N$2.

Divide by the window length, not by the number of days with an issue. VAL-12 was issued only three times, but it was being used across all 90 days.

=H2/N2

Result: 4

4: VAL-12 had 360 units issued in the last 90 days, so it uses 4 a day on average.

Days on hand, and the zero-usage trap

Days on hand in J2 is =F2/I2: 52 valves at 4 a day last 13 days. HOS-30 has 210 hoses at 1.5 a day, which is 140 days: overstock worth questioning. FLT-05 has 180 filters at 10 a day, 18 days.

GKT-90 hasn't been issued in the window (its only issue was on 20 July), so its usage is 0 and the plain division returns #DIV/0!. That error is information, not noise: the item isn't moving. Show it in words with =IFERROR(F2/I2,"No usage"), and don't turn it into 0, which would read as "we're about to run out".

=F5/(H5/N2)

Result: #DIV/0!

#DIV/0!: GKT-90 has 75 on hand and no usage in 90 days. J5 shows "No usage" instead.

Flag what to reorder and what's dead

Compare days on hand with how long a replenishment takes. Keep each SKU's lead time in G and a safety buffer in days in an input cell (N3, 7 days), so the rule is easy to tune. The flag in K2: =IF(H2=0,IF(F2>0,"Dead stock",""),IF(J2<G2+$N$3,"Reorder","OK")).

VAL-12 has 13 days of cover but needs 14 days to replenish plus 7 safety days, so it's flagged Reorder. To cover lead time plus safety it needs (14 + 7) x 4 = 84 units; with 52 on hand, =MAX(0,ROUNDUP((G2+$N$3)*I2-F2,0)) suggests ordering 32. GKT-90 is flagged Dead stock: something to sell off, return or write down, not to reorder. List all reorders in Excel 365 with =FILTER(E2:E5,K2:K5="Reorder"). To order up to a max level instead of a fixed cover target, see the min and max section in Excel formulas for inventory.

=FILTER(E2:E5,K2:K5="Reorder")

Result: VAL-12

VAL-12: the only SKU whose days on hand (13) is below its lead time plus safety days (21).

Pick the window on purpose: 90 days vs 30 days

A 90-day window smooths out lumpy orders; a 30-day window reacts faster when demand changes. Change N2 to 30 and FLT-05 tells a different story: 600 filters went out in the last 30 days, 20 a day, so the 180 on hand last only 9 days, below its 10-day lead time.

A sensible habit is to run both and act on the shorter cover for items with rising demand. For seasonal items, use the same period last year instead of the last 90 days. And keep your units consistent: if lead times are quoted in working days, count usage per working day too.

=F4/(SUMIFS(C2:C11,B2:B11,"FLT-05",A2:A11,">"&N1-30,A2:A11,"<="&N1)/30)

Result: 9

9: FLT-05's days on hand using only the last 30 days of usage, against 18 days on the 90-day window.

Why company-wide DIO gives a different number

Finance reports days inventory outstanding (DIO) as average inventory value / cost of goods sold x 365. With 84,000 of average inventory and 610,000 of COGS in the last 12 months, =N5/N6*365 gives about 50.3 days.

That's a money-weighted average across everything you stock, so it can look healthy while a fast mover is about to run out and a slow mover sits for months. Use DIO for the trend and for comparing periods; use per-SKU days on hand to decide what to buy. Averaging the SKU column (=AVERAGE(J2:J5) gives 57) mixes items with different values and means very little.

=N5/N6*365

Result: 50.262295082

50.26: company-wide days inventory outstanding from inventory value and COGS.

Common mistakes

Dividing usage by the number of issue days

Divide by the full window length (N2), whether or not the item moved every day.

Turning #DIV/0! into 0

Show "No usage" and flag dead stock; 0 days would mean "out of stock now".

Hard-coding 90 and the date into every formula

Keep report date, window and safety days in input cells so the whole sheet updates.

Comparing days on hand with lead time in different units

Use calendar days for both, or working days for both.

FAQ

What is the formula for days of inventory on hand?

For one item: units on hand / average daily usage. For the whole business: average inventory value / cost of goods sold x 365 (or the number of days in the period).

How do I calculate average daily usage in Excel?

Sum the quantity issued in a fixed window with SUMIFS and divide by the window length: =SUMIFS(qty,sku,E2,date,">"&report_date-90,date,"<="&report_date)/90.

How do I avoid #DIV/0! in days on hand?

Wrap the division in IFERROR: =IFERROR(F2/I2,"No usage"). Items with zero usage aren't errors to hide; flag them as possible dead stock.

Is days of inventory on hand the same as inventory turnover?

They're two views of the same idea. Turnover is how many times inventory is used up per year; days on hand is roughly 365 divided by turnover.

Functions used

Related guides

Updated