CODEWITHSUNDEEP
exceldynamicexcel-formula
quinekxi
quinekxi

Reputation: 889

How to exclude zero entry from a dynamic formula (SUBTOTAL + SUMPRODUCT) in excel

I'm working on a formula to get the standard deviation. It has been working not until I encountered a zero value which makes the result into #DIV/0!.

This is the screenshot of the expected value.

enter image description here

However, when I used my formula, the Game Time SD returned 0.

How do I exclude it in the calculation if the value in F column is zero? I tried IF(F5:F9 <> 0) but it won't work.

enter image description here

This is the formula I used.

  1. F3 = IFERROR(SUBTOTAL(1,F5:F9),0)
  2. G3 = IFERROR(SUMPRODUCT(SUBTOTAL(2,OFFSET(F5:F9,ROW(F5:F9)-MIN(ROW(F5:F9)),,1))*(G5:G9*F5:F9))/SUBTOTAL(9,F5:F9),0)
  3. H3 = IFERROR(((SUBTOTAL(9,F5:F9)*(SUMPRODUCT(SUBTOTAL(2,OFFSET(F5:F9,ROW(F5:F9)-MIN(ROW(F5:F9)),,1)) * ((H5:H9^2*F5:F9*(F5:F9-1)+(G5:G9*F5:F9)^2)/F5:F9)))-(SUMPRODUCT(SUBTOTAL(9,OFFSET(G5:G9,ROW(G5:G9)-MIN(ROW(G5:G9)),,1)),SUBTOTAL(9,OFFSET(F5:F9,ROW(F5:F9)-MIN(ROW(F5:F9)),,1))))^2)/(SUBTOTAL(9,F5:F9)*(SUBTOTAL(9,F5:F9)-1)))^(1/2),0)

I know the problem is somewhere in F5:F9, since the divisor used is zero

Upvotes: 1

Views: 1503

Answers (1)

A.S.H
A.S.H

Reputation: 29352

The part you suspected in the code involves dividing by a denominator that happens to be a factor in the numerator. You can avoid a division by zero by simplifying that fraction.

((H5:H9^2*F5:F9*(F5:F9-1)+(G5:G9*F5:F9)^2)/F5:F9)))

can be reduced to

(H5:H9^2*(F5:F9-1) + (G5:G9^2*F5:F9))

Resulting in the formula (3rd line modified)

=IFERROR(((SUBTOTAL(9,F5:F9)*
(SUMPRODUCT(SUBTOTAL(2,OFFSET(F5:F9,ROW(F5:F9)-MIN(ROW(F5:F9)),,1))*
(H5:H9^2*(F5:F9-1) + (G5:G9^2*F5:F9))))-
(SUMPRODUCT(SUBTOTAL(9,OFFSET(G5:G9,ROW(G5:G9)-
MIN(ROW(G5:G9)),,1)),SUBTOTAL(9,OFFSET(F5:F9,ROW(F5:F9)-
MIN(ROW(F5:F9)),,1))))^2)/(SUBTOTAL(9,F5:F9)*
(SUBTOTAL(9,F5:F9)-1)))^(1/2), 0)

In my tests without the enclosing IFERROR, I could set some rows to zero and get values. Only when the square rooted subtotal was negative (which logically should not happen) was the result #NUM.

Hope this helps.

Upvotes: 2

Related Questions