Excel guide · 4 min read
How to calculate days of inventory on hand in Excel
=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.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Issue date | SKU | Qty issued | SKU | On hand | Lead time (days) | Usage in window | Avg daily usage | Days on hand | Flag | ||
| 2 | Jul 20, 2026 | GKT-90 | 40 | VAL-12 | 52 | 14 | 360 | 4 | 13 | Reorder | ||
| 3 | Aug 18, 2026 | VAL-12 | 120 | HOS-30 | 210 | 21 | 135 | 1.5 | 140 | OK | ||
| 4 | Aug 30, 2026 | HOS-30 | 45 | FLT-05 | 180 | 10 | 900 | 10 | 18 | OK | ||
| 5 | Sep 9, 2026 | FLT-05 | 300 | GKT-90 | 75 | 30 | 0 | 0 | No usage | Dead stock | ||
| 6 | Sep 22, 2026 | VAL-12 | 150 | |||||||||
| 7 | Oct 3, 2026 | HOS-30 | 60 | |||||||||
| 8 | Oct 14, 2026 | FLT-05 | 280 | |||||||||
| 9 | Oct 20, 2026 | VAL-12 | 90 | |||||||||
| 10 | Oct 31, 2026 | FLT-05 | 320 | |||||||||
| 11 | Nov 6, 2026 | HOS-30 | 30 |
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.
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".
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.
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.
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.
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
SUMIFSAdds with several conditionsIFERRORFallback when a formula errorsIFPicks a result from a testFILTERKeeps matching rows, spillsMAXLargest numberROUNDUPRounds away from zeroAVERAGEMean of numbersDATEBuilds a date
Related guides
- Excel formulas for inventory: stock balance, reorder and value
- How to calculate inventory turnover in Excel
- How to calculate safety stock and reorder point in Excel
- How to find dead stock in Excel
- How to calculate stockout rate in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate the payback period in Excel
Updated