how to filter out irrelevant results

[bigdan]

I'm often comparing Actual $ Results vs what was planned. The formula for this is ((Actual - Plan)/Plan).

In some cases the Plan was zero, this giving an error. To fix that I've added an IFERROR(formula,0), which has fixed that problem.

However in some cases the Plan isn't exactly zero but very close. So with a denominator this small the quotient is like 10 figures long, which is essentially useless.

What do I do to fix the latter case? I'd like that to be zero as well.

 

[Gerald Higgins]

Let's say Actual is in A1, and Plan is in A2.
You need to decide how close to zero can be considered to be zero for your purposes, let's say it's the value 0.1, and let's say you store this value in A3.

Your formula could then be

=if(A2< A3,0,(A1-A2)/A2)

This assumes you will never have negative plan values - if you do, there are ways round this.

 

[bigdan]

OK great, thank you.

As an alternative method is there some way to set a limit on how big the result is?

Let's say Actual is in A1, and Plan is in A2.
You need to decide how close to zero can be considered to be zero for your purposes, let's say it's the value 0.1, and let's say you store this value in A3.

Your formula could then be

=if(A2< A3,0,(A1-A2)/A2)

This assumes you will never have negative plan values - if you do, there are ways round this.

 

[Gerald Higgins]

Can you give some examples of what exactly you want to do ?

By the way, I think there is no need to quote my post

 

[bigdan]

OK Sure. So in one case the forecast was like a hundredth of a cent. Or maybe a millionth of a cent. Not sure how on earth that happened but that's in there somehow and I cant control it. The numerator is a few million. So the quotient is something like 123456789887654321%. That's essentially a useless number. So I'd like to cap the quotient off at 3-4 digits.

 

[Gerald Higgins]

Well I think my solution in post #2 addressed that.

Can you give us some specific examples where you think my solution from post #2 is not working ?

Post several examples please, showing what's in each of A1, A2, and A3, and if they are not giving the result you want, say what you think the result should be, and why.