Forum Discussion
Need Help with Microsoft IF Formula
I need to build an =IF Formula that will return the result as per the following permutations :
Column A - Name of School
Column B - Status of Faculty
Column C - Faculty Fee
Column D - Admin Fee
I need an IF formulae that will combine 2 things : When a Status of Faculty is "External" OR When the Status of Faculty is "Adjunct" + Name of School is "Business" Leave the Admin Fee Column Blank. If not Multiple the Faculty Fee by 10% to get the Admin Fee.
Can this be achieved with an IF Formula ?
Thank you very much for your expert help !
I can only guess where it might go wrong.
=IF(OR(O64="External",AND(O64="NUS-Adjunct",G64="School of Business",0,S64*0.1)
There should be an equal number of opening and closing parentheses. But you have three opening and only one closing. IF that's the issue (although it's possible you just didn't copy it here as it really is) the formula should read
=IF(OR(O64="External",AND(O64="NUS-Adjunct",G64="School of Business")),0,S64*0.1)
If that's not the case, maybe you could be more descriptive on exactly how it's not working. Is it delivering no result, a wrong result (if so, what wrong result)? And so forth. "Not working" is too vague to make a diagnosis.
4 Replies
See the attached. The formula you requested is as follows
=IF(OR([@Status]="External",AND([@Status]="Adjunct",[@School]="Business")),0,[@[Faculty Fee]]*0.1)
However, if in fact that was just scratching the surface, IF, that is, you have still more conditions that need to be handled, it would be far better to create a table and a formula that accesses the table to determine what action to take. IF formulas, when too many possible combinations are involved, can get indecipherable and thereby unreliable. Even if they work as desired at the start, a small policy change can wreak havoc.
- Actually, this is the Formula I did using your Example above :
=IF(OR(O64="External",AND(O64="NUS-Adjunct",G64="School of Business",0,S64*0.1)
But it does not work 😞 Can you tell me where I am going wrong ?I can only guess where it might go wrong.
=IF(OR(O64="External",AND(O64="NUS-Adjunct",G64="School of Business",0,S64*0.1)
There should be an equal number of opening and closing parentheses. But you have three opening and only one closing. IF that's the issue (although it's possible you just didn't copy it here as it really is) the formula should read
=IF(OR(O64="External",AND(O64="NUS-Adjunct",G64="School of Business")),0,S64*0.1)
If that's not the case, maybe you could be more descriptive on exactly how it's not working. Is it delivering no result, a wrong result (if so, what wrong result)? And so forth. "Not working" is too vague to make a diagnosis.
