IF OR AND function

Ian Bell
Ian Bell used Ask the Experts™
on
Hi
I would like help with placing brackets in right place and any other help on the below formula.
Example  ....... COL D contains numbers 1-12 and only numbers = 2,3 and 5 are of interest,
IF(OR(AND(D2=2,D2=3,D2=5),E2=1,F2=4,G2=5),99,0)
sample
                      D2 value = 2     E2 value = 1    F2 value = 4    G2  value = 5      returned value  = 99  all conditions fulfilled
                      D2 value = 1     E2 value = 1    F2 value = 4    G2  value = 5      returned value  = 0
                      D2 value = 2     E2 value = 4    F2 value = 4    G2  value = 5      returned value  = 0
                      D2 value = 3     E2 value = 1    F2 value = 4    G2  value = 5      returned value  = 99  all conditions fulfilled

Please help by modifying formula
Thanks
Ian
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014
Commented:
IF(AND(OR(D2=2,D2=3,D2=5),AND(E2=1,F2=4,G2=5)),99,0)

Open in new window

updated
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
You've just reversed AND and OR:
IF(AND(OR(D2=2,D2=3,D2=5),E2=1,F2=4,G2=5),99,0)

Open in new window

Ian Bellretired

Author

Commented:
Thanks guys you were quick.
Works perfectly
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ian Bellretired

Author

Commented:
It's worked perfectly at my end.
I ran it over 72,000 rows and carried out an independent check and it all matched.
Thanks
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Sorry.
aikimark changed his comment several times while I was looking at it (but not having posted yet), and I had the original comment in view only.
The comment as posted works. Whether you prefer his or my formula is up to you - please reassign points as you see fit.
Ian Bellretired

Author

Commented:
I wasn't aware of that. When I first accessed to see replies Aikimark's answer was complete.
I awarded the points based on his being first on list. However due to your latest post it probably is fair to award points equally. Assuming of course Aikimark has no objection.

Thanks again chaps

Ian
Top Expert 2014

Commented:
No objection.  I thought I had made my change quick enough.  I guess that was a bad assumption on my part.  Sorry for the confusion.
Ian Bellretired

Author

Commented:
I can now sleep peacefully :)   thanks A

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial