Search code examples
excelerror-handlingexcel-formulaexcel-tablesvba

div to 0 handling excel

So I have this issue, I have two tables one is employees, and another one is the projects.

Employees Table:

Year Name Type Jan Feb    
2018 Kevin Salary 5000 2000    
2018 Kevin Insurance 200 400    
2018 Alex Salary 3000 4000    
2018 Alex Insurance 300 400

Projects Table

Year Project_Name Employee_Name Jan_Hours_Worked Feb_Hours_Worked    
2018 Apple Alex 7 5    
2018 Apple Kevin 5 0    
2018 LG Kevin 0 3

Now I am creating a result list of all the projects and costs recurred for them in each month, what I need is for each project in Table 2 to find which employees are involved and then use that info to find costs from the Table 1 and calculate the hourly rate to charge for the projects in each month.

(e.g for project LG, I have Kevin working on that in Feb,for him company paid 2400(salary+insurance) and the hourly rate to charge in Feb then will be 2400 divided by total hours he spends on the project which is 3 hours.

For the project Apple in Feb, it would be Alex's: 4400/5, since Kevin didn't work any hours in Feb in this project. And when both of them works for example as in Jan for the apple project then it should be Kevin's costs/hours he worked + Alex's costs/hours he worked in that month for that project.

Now I have the formula to calculate this which works fine unless I have the 0 value in the table.

When it comes to the 0 it gives me division to zero error

=SUMPRODUCT(SUMIFS(Employees[Feb], Employees[Name],Project[Employee_Name], Employees[Year], 2018 )/Project[Feb_Hours_Worked],--(Project[Project_Name]=K14))

How can I alter this formula to make that work for the above scenario without getting error and finding correct result for each month.

Solution

  • You are not making this easy but I think I found something suitable :

    enter image description here

    Edit:

    For DIV 0 Error, using the same set up as above in M9 cell :

    {=SUMPRODUCT(($J$2:$J$4=$K10)*(M$2:M$4*(IFERROR(SUMIF($B$2:$B$5,$K$2:$K$4,E$2:E$5)/SUMIF($K$2:$K$4,$K$2:$K$4,M$2:M$4),0))))}

    Validation with CTRL + SHIFT + ENTER

    Edit 2 :

    enter image description here

    =SUMPRODUCT(M$2:M$9,(IFERROR(SUMIFS(E$2:E$17,$A$2:$A$17,$P5,$B$2:$B$17,$K$2:$K$9)/SUMIFS(M$2:M$9,$I$2:$I$9,$P5,$K$2:$K$9,$K$2:$K$9),0))*($I$2:$I$9=$P5)*($J$2:$J$9=$Q5))

    Validation with CTRL + SHIFT + ENTER