How to use IFERROR - example provided
Requirement:
Below is an example of what the user is working on. So for each month the user has the number of Work Orders and the total labour hours.
At the end the user want to show the average Labour Hours per Work Order.
That's simple: =SUM(B3,D3,F3,H3,J3,L3,N3,P3,R3,T3,V3,X3)/SUM(A3,C3,E3,G3,I3,K3,M3,O3,Q3,S3,U3,W3)
But if there are only zeros then naturally it will give back an error (#DIV/0!)
Can someone tell me how I can incorporate an IFERROR or IFISERROR into the above formula so it will show all errors as a 0?
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |
| 1 | Apr-18 | May-18 | Jun-18 | Jul-18 | Aug-18 | Sep-18 | Oct-18 | Nov-18 | Dec-18 | Jan-19 | Feb-19 | Mar-19 | |||||||||||||
| 2 | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | No of WO | Lab Hrs | Lab Hrs/WO |
| 3 | 0 | 0 | 0 | 0 | #DIV/0 | ||||||||||||||||||||
Solution:
=IFERROR(SUM(B3,D3,F3,H3,J3,L3,N3,P3,R3,T3,V3,X3)/SUM(A3,C3,E3,G3,I3,K3,M3,O3,Q3,S3,U3,W3),0)
Obtained from the OzGrid Help Forum.
Solution provided by royUK.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use IFERROR, ROUND and SUM in one formula |
| How to use Formula: =IF ERROR |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
