excel question - how to do a formula for % variance

I know how to use if statements in excel, but im wondering how to do % variance using if.

e.g.

two numbers

• A1 = 100
• A2 = 99

I am trying to find out whether the 2nd number is within 2% of the first (which is true in this example)

A开发者_JAVA百科ny maths geniuses know where to start with this?

Cheers

ke

The most straight forward way (using simple math only and not excel percentage functions):

=IF(AND(A2>(A1-(A1*2/100)),A2<(A1+(A1*2/100))),"yes","no")

You can switch the >,< with >=,<= respectively if exactly 2% away is also good...

Asking if A2 is within 2% of A1 is the same as asking if A2 / A1 lies within the range 0.98 to 1.02:

IF( AND( 0.98 <= (A2 / A1), (A2 / A1) <= 1.02 ), <true>, <false> )

Note I have used 'number line order' here to make it easier to see that we are looking for A2 / A1 to lie between the two outer numbers.