Explanation
This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we’re doing:
=AVERAGE(first:last)
where “first” is a reference to the first cell to include in the average and “last” is a reference to the last cell to include. The result is a range that includes the N cells to average.
To get the first cell in the range, we use INDEX like this:
INDEX(Table1[Sales],ROWS(Table1)-(F4-1))
The array is the entire Sales column, and row number worked by subtracting (n-1) from total rows.
In the example, F4 contains 3, so the row number is 10-(3-1) = 8. With a row number of 8, INDEX returns C12.
To get the last cell we use INDEX again like this:
INDEX(Table1[Sales],ROWS(Table1))
There are 10 rows in the table, so INDEX returns C14.
The AVERAGE function then returns the average of C12:C14, which is $78.33.
Explanation
This formula demonstrates a very simple inventory concept where current inventory is simply the result of all incoming stock minus all outgoing stock. In the example, colors are treated as unique item identifiers – imagine a product available in one size only in just three colors: red, blue, or green.
The key to this approach is to use Excel Tables , because Table ranges automatically expand to handle changes in data. This means we can get a total of all incoming red items with:
=SUMIFS(In[Qty],In[Color],J7)
And a total of all outgoing red items with:
=SUMIFS(Out[Qty],Out[Color],J7)
In both cases, the SUMIFS function generates a total for all red items in each table.
Then, as long as both tables are up to date and complete, we can get the current inventory of red items with the following formula:
=SUMIFS(In[Qty],In[Color],J7)-SUMIFS(Out[Qty],Out[Color],J7)
As the formula is copied down, we get current inventory for each color.
