Lessons Lesson 104

Project: A Stock Reorder List

About this lesson

A reorder list is an IF flag comparing stock with its reorder level, and a lookup from a code list for the supplier. Conditional functions over the flag column say what to chase.

By the end you can

  • flag what to order, name the supplier, and list what to chase.
  • summarise a flag column with COUNTIF, MAXIFS and COUNTIFS.

Practises IF XLOOKUP COUNTIF MAXIFS COUNTIFS

By Ali, Founder, Excel Lessons

The idea

The stockroom's sheet. A flag per row from one comparison, a supplier name from a code list, and then the questions. How many to order, which will take longest, which are both needed and slow. Every one of them reads the flag column. That is why it is built first.

The not-found text in the XLOOKUP is doing real work. A supplier code that is not on the list is exactly the row somebody has to look at. Unknown says so, where #N/A would just look broken.

The mistake to watch for

A supplier code that is not on the list showing as #N/A. That row is exactly the one somebody has to look at. An error looks broken, not informative. XLOOKUP's not-found text says Unknown where the person can see it. Build the flag column first, because every summary reads it.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In F2, type:

=IF(B2<C2,"Order","ok")

This fills down to F6.

F2
Row A B C D E F G H I J
1 Part Stock Reorder at Lead days Supplier Action Supplier name Code Name
2 Hinge 140 50 3 S1 S1 Ironmonger
3 Bracket 32 40 7 S2 S2 Fixings Ltd
4 Bolt M6 910 300 2 S1
5 Castor 8 12 10 S2
6 Handle 25 30 14 S9
7
8
9 Parts to order
10 Longest lead

Every step

  1. 1

    In F2, type: =IF(B2<C2,"Order","ok"). This fills down to F6.

    Hint. Stock against reorder level.

  2. 2

    Read =IF(B6<C6,"Order","ok") and say what it returns.

    Hint. Below the level.

  3. 3

    In G2, find the supplier name in I1:J3 with XLOOKUP. Shape: =XLOOKUP(lookup, lookup_range, return_range, if_not_found). The fourth argument covers a code that is not listed. Remember: dollar signs lock a range so it doesn't move.

    Hint. Fourth argument for the missing code.

  4. 4

    In B9, count the parts that need ordering with COUNTIF.

    Hint. Count the "Order" rows.

  5. 5

    In B10, find the longest lead time for parts on order with MAXIFS.

    Hint. Lead days, filtered by Order.

  6. 6

    In B11, count parts on order with a lead over 7 days using COUNTIFS. Those are the ones to chase first.

    Hint. "Order" and ">7".