Need help creating a formula in Excel

  ljones 00:35 30 Mar 2010
Locked

Hi,

I'm hoping that someone out here can help me with my little problem. I need to create a formula that will allow me to identify where a figure in cell A1 when compared to a figure in cell A2 is anywhere between 5% and 10% difference either + or -.
I know it sounds messy but that was the best written description I could come up with.

Thanks in anticipation of some much needed help and/or advice.

  VoG II 09:11 30 Mar 2010

Perhaps

=AND(ABS((A1-A2)/A2)>=0.05,ABS((A1-A2)/A2)<=0.1)

  fishmad pete 10:18 30 Mar 2010

=IF(AND(A1/B1>=0.05,A1/B1<=0.1),TRUE,IF(AND(A1/B1<=-0.05,A1/B1>=-0.1),TRUE,FALSE))

I think the above formula would work.it uses a nested "if" statement. It first uses an "and" function to check that positive value lies between 5 and 10 percent. if that fails it then checks for a negative. If it finds one or the other it returns a result of true if not false.
You can change the true and false statement to what ever you want. If you want text be sure to use quotation marks

Regards
Peter

  ljones 17:29 30 Mar 2010

Thanks to you both for the responses. I now can complete my accruals and keep the boss happy.
Cheers

  shellship 17:39 30 Mar 2010

Is fishmad pete a competitor of VoG™?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Graphic tees: 14 best websites to find your next T-shirt

How to update iOS on iPhone or iPad

Les meilleures applications pour enfants 2017