Selected Answer
I want to suggest three ways of resolving the division error in ((H6/F6)-$H$2)*F6 if F6 evaluates to zero.
=((H6/IF(F6,F6,1))-$H$2)*F6
This is the most direct approach. It just avoids the use of zero. In your case I think it may not work because H6-H2 might produce an unacceptable result.=IFERROR(((H6/F6)-$H$2),0)*F6
This formula allows the error to occur but substitutes 0 for the result if there is an error. The first part of the formula returns zero in case of error and zero multiplied by zero (in F6) will deliver a result of zero.
Of course, this looks convoluted. Therefore the ISERROR() function might be placed differently. In this case the result =0 if any error occurs in the formula.
=IFERROR(((H6/F6)-$H$2)*F6,0)
- The underlying logic, "return 0 if F6 = 0", gives raise to the third solution which is its direct interpretation in Excel syntax.
=IF(F6,((H6/F6)-$H$2)*F6,0)
Note that the expression IF(F6, ... is paramount to IF(F6<>0, ... IF(F6 returns True if F6 holds any number other than zero.
Of course, you would need to replicate this formula for each row and just add up the results of each as you are now doing. However that doesn't seem like the best possible solution. Normally, one would have a column for a line total and a sum of the line totals at the end of that column. A running total on each row would be constructed the same way. But if you are trying to do without a column for line total (even a hidden one) one would use the SUMPRODUCT() function to make the total or an array formula. Perhaps, since it doesn't really relate to the subject here, you like to make that your question in another question.
