Basic FunctionsBeginner

ABS Function

How far off you are, not which direction.

Task:

Stocktake is in and the counted figures disagree with the system. You need the size of each discrepancy to decide what is worth investigating, and being 12 short matters as much as being 12 over. Put the variance for each item in column D.

Interactive Spreadsheet

Read about ABS

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of ABS

0/3

The same formula in the other shapes it takes at work.

  1. ABS Function

    How far off you are, not which direction.

    CoreBeginner
  2. ABS for a tolerance check

    Too high and too low are the same problem: too far from the target.

    Intermediate
  3. ABS for a percentage error

    How wrong was the forecast, ignoring which way it was wrong?

    Intermediate

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

5 rows × 4 columns4 cells you fill in
ABCD
1ItemSystemCountedVariance
2Blue ink cartridge120114
3A4 paper (reams)340351
4Stapler4545
5Desk lamp7866

Functions used here

What this exercise teachesMay contain the answer

Without ABS, a shortfall and an overage cancel each other out the moment you total the column, and a shelf that is 12 under looks identical to one that is 12 over. Taking the magnitude first is what makes the variance column worth sorting.