Excel track · Exercise 28 of 86 Paid
Logical functions: putting the pieces together
Combine a logical test, a combined test and a rounded calculation in one formula.
Advanced
Stock list with a total row, and a price column to average
Read-only data · screen 1 of 2 · answer in B9
In B9, put PASS in the cell when the total is over 100 and no item is under 15 units, otherwise FAIL.
| A | B | |
|---|---|---|
| 1 | Item | Units |
| 2 | Bolt | 18 |
| 3 | Nut | 22 |
| 4 | Washer | 25 |
| 5 | Screw | 30 |
| 6 | Clip | 24 |
| 7 | Pin | 31 |
| 8 | Total | 150 |
| 9 | Verdict |
In D8, average the price column and round it to one decimal, but say none when there is nothing to average.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Units | Price | Answer |
| 2 | Bolt | 18 | 4.5 | |
| 3 | Nut | 22 | 3.9 | |
| 4 | Washer | 25 | 4.2 | |
| 5 | Screw | 30 | 5.1 | |
| 6 | Clip | 24 | 3.4 | |
| 7 | Pin | 31 | 4.8 | |
| 8 | Total | 150 | ||
| 9 | Average price |
Reference answer
What it does
The two formulas here put the earlier pieces together. The first nests AND inside IF: both tests are checked and the cell answers PASS or FAIL rather than TRUE or FALSE. The second wraps an average in ISNUMBER before rounding it, so a price column with no numbers returns none instead of an error.
On the first screen the total is 150 and the smallest item is 18, so both tests hold and B9 reads PASS. The second screen averages six prices to 4.3166 and rounds that to 4.3. Every figure comes from the grid, so a corrected price moves the answer without the formula changing.
ROUND changes the value while cell formatting changes only the look, so a rounded figure can be summed and a formatted one still carries its decimals underneath. A guard on the calculation names the condition that could fail, which reads better than IFERROR over the whole formula.
Syntax
=IF(AND(test 1, test 2),"PASS","FAIL"); =IF(ISNUMBER(AVERAGE(range)),ROUND(...,1),"none")Worked example
On Stock list with a total row, and a price column to average, =IF(AND(B8>100,MIN(B2:B7)>=15),"PASS","FAIL") returns PASS.
=IF(AND(B8>100,MIN(B2:B7)>=15),"PASS","FAIL")Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The finished text result was used in arithmetic, as when PASS is divided by zero. |
#DIV/0! | AVERAGE found no numbers in C2:C7 and divided a zero total by a count of zero. |
#NAME? | One of the nested function names was mistyped, so the whole formula fails. |
At work
Printing a rounded average with a word in place of an error when a month has no data.