Need help creating a formula in Excel

  ljones 00:35 30 Mar 10
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 10

Perhaps

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

  fishmad pete 10:18 30 Mar 10

=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 10

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

  shellship 17:39 30 Mar 10

Is fishmad pete a competitor of VoG™?

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

Hands-on: Samsung Galaxy S8 review

1995-2015: How technology has changed the world in 20 years

Samsung's beautifully designed Galaxy S8 makes for better VR experiences too

47 iPhone camera tips to help you take better photos