Excel track · Exercise 84 of 86 Paid

An inventory reorder report

Floor a row's shortfall at zero, then net the whole sheet the same way.

Advanced

Four parts with stock, reorder points and one answer cell

Read-only data · screen 1 of 2 · answer in D2

In D2, report how many bolts to order, never a negative number.

	A	B	C	D
1	Item	On hand	Reorder at	Answer
2	bolt	4	10
3	nut	12	8
4	washer	2	6
5	screw	9	15

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In D2, add up how far the sheet falls short of every reorder point.

	A	B	C	D
1	Item	On hand	Reorder at	Answer
2	bolt	4	10
3	nut	12	8
4	washer	2	6
5	screw	9	15

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

MAX returns the largest number it is given, so MAX(0,C2-B2) reports a shortfall and never a negative. Where the reorder point sits above stock, the subtraction stands. Where stock is higher, the zero wins. The bolt row has ten to reorder against four on hand, so it asks for six.

The second screen asks the same question of the whole sheet: the reorder column comes to 39, the stock column to 27, and the net shortfall is 12. MAX holds that at zero in a month when the warehouse is ahead. The nut's surplus cancels part of the washer's gap, which is why flooring the rows one by one would add to 16.

The guard matters because the column has to fill down. Copy C2-B2 into four rows without it and the nut asks for minus four, which no purchase order can carry. With the zero in place the same fill gives figures you can add straight into the order.

Syntax

=MAX(0,reorder_at-on_hand) floors a shortfall at zero; MAX(0,a-b) over totals nets it out

Worked example

On Four parts with stock, reorder points and one answer cell, =MAX(0,C2-B2) returns 6.

=MAX(0,C2-B2)

Common errors

Error codes for MAX as a zero floor, over one row and then over two column totals
Error	What it means and how to fix it
`#VALUE!`	A cell in the subtraction holds text, which is not a number to work with.
`#NAME?`	MAX is misspelled in the call, and Excel returns #NAME?.
`Negative order`	MAX is left off, so an over-stocked row asks for a negative number of parts.

At work

Turning on-hand stock and reorder points into an order quantity that never goes negative.