Finding Variances with 0

  • Thread starter Thread starter Firecraker
  • Start date Start date
F

Firecraker

I have a spreadsheet that calculates Actuals (Col A) vs. Projection
(Col B) and then a variance (Col C). I'm running into some problem
when I'm dividing by zero. I tried ISERROR and it works when bot
cells are 0 giving me a 0% variance. But, when there's a number in th
actuals and a 0 in the projections it gives me a 0% variance when
need an actual variance. For example...
If a 1 is in the Actuals (A2), and a 0 is in the projections (B2)
have to show a variance of 100% (C2).

Any help would be hugely appreciated...

Thank you in advance..
 
I have a spreadsheet that calculates Actuals (Col A) vs. Projections
(Col B) and then a variance (Col C). I'm running into some problems
when I'm dividing by zero. I tried ISERROR and it works when both
cells are 0 giving me a 0% variance. But, when there's a number in
the actuals and a 0 in the projections it gives me a 0% variance when
I need an actual variance. For example...
If a 1 is in the Actuals (A2), and a 0 is in the projections (B2) I
have to show a variance of 100% (C2).

Any help would be hugely appreciated...

Thank you in advance...
Click to expand...

Would something like this work in C2...

=If(B2,(A2-B2)/B2,IF(A2,1,0))

assuming (A2-B2)/B2 is the formula you're using for varience and C2 is
formated as a %.

Hope this helps.

Best,
Bill
 
Back
Top