Pay attention to the data below. A store sells five products. The beginning stock of each product is 100 pcs. At the end of the month, the clerk calculates the end stock of each product (see column C) and the money earned for each product (see column D).
What is the selling price of each product and what is the average selling price for all products sold in that shop?
To calculate the selling price, divide the money earned with the items sold.
The Formula
=D3/(B3-C3)
Do a copy (CTRL+C) in cell E3 the do a paste (CTRL+V) in range E4:E7.
The Result
There is a divide by zero error in cell E5 (no items sold for product C).
***
To find out the average selling price of the five products, you can use the AVERAGE function.
The Formula
=AVERAGE(E3:E7)
The Result
It turns out that the AVERAGE function also returns a divide by zero error. How to solve this problem? Errors in cell E5 cause the same error when calculating the average selling price.
Solution #1, using IF Function
This solution is used to avoid an excel divide by zero error from appearing, by modifying a formula that calculates the selling price. If the items sold are equal to 0, no need to do a division operation, otherwise do a division operation.
The Formula
=IF(B3-C3=0,"No Sales",D3/(B3-C3))
The Result
If there are no sales, the formula above displays “No Sales”, you can choose other words, but don’t use a number (i.e., 0 or other numbers), because will affect the calculation of the average selling price.
When the formula for calculating the selling price is modified, a #DIV/0! error in cell E5 disappears. The same error in cell E9 also disappears, displays the average selling price for the five products.
Solution #2, using IFERROR Function
Just like solution #1, solution #2 avoid divide by zero error from appearing.
The Formula
=IFERROR(D3/(B3-C3),"No Sales")
The Result
The result is the same as solution # 1, the divide by zero error disappears in cells E5 and E9.
Other alternatives, you can use the IF function and the ISERROR function, the results are the same.
The Formula
=IF(ISERROR(D3/(B3-C3)),"No Sales",D3/(B3-C3))
Solution #3, using AGGREGATE Function
Solution #3 is different from the two previous solutions. This solution lets the excel divide by zero error appear. Change the formula in cell E9 from the AVERAGE function to the AGGREGATE function.
The Formula
=AGGREGATE(1,6,E3:E7)
The Result
The Excel divide by zero error still appears in cell E5, but cell E9 displays the correct average selling price.
The solution is in options argument; there are several choices, number 6 is “Ignore error values”, meaning if there is an error in range E3:E7 the AGGREGATE function will ignore it.
Solution #4, using Array Formula
Solution #4 is the same as solution #3, leaving the excel divide by zero error still there and modifying the formula to calculate the average selling price using an array formula.
The Formula
=AVERAGE(IFERROR(E3:E7,"No Sales")) The formula above is an array formula. It must be ended by pressing the CTRL+SHIFT+ENTER key together.
The Result
How does the array formula work?
Solution #4 uses the same function as solution #2, i.e., the IFERROR function and the AVERAGE function. Solution #2 uses the IFERROR function first to calculate the selling price of each product, then use the AVERAGE function to calculate the average selling price.
The array formula in solution #4 can combine both functions in one formula. To find out how does the formula works, edit the formula in cell E9.
Block the IFERROR function, then press the F9 key.
The IFERROR function changes to an array with 5 data 10, 12, “No Sales”, 14, 15. Compare this with the results of the IFERROR function in solution #2, can you find any differences? The result is the same. The five data are calculated by the AVERAGE function to get the average selling price.
Block the AVERAGE function, then press the F9 key. The result is the average selling price.
The animation below explains how array formula array work.
Which One is the Best Solution?
If you want the excel divide by zero error does not appear, then use solution #1 or solution #2. Both return the same results. Which one is the best? It’s up to you, choose one for your solution.
If you want to keep the excel divide by zero error, then use solution #3 or solution #4. For me, solution #3 is easier; it’s only an Excel function with three arguments. Solution #4 is more complicated then solution #3; it involves two functions and an array formula. My advice is to use the AGGREGATE function.
When to choose an array formula compared to the AGGREGATE function?
The AGGREGATE function only provides 19 functions; if the desired function not in the list, then it’s time to use an array formula. Array formula able to use all functions that Excel has.
