Referencing a cell to determine operator in IF Logical Test

**JGrange** · 12-06-2014, 02:34 PM

Hello

So I have a spreadsheet that works out our team KPIs.

What I want to do is create an IF statement that checks a cell to determine whether the operator in the logical test should be > or <.

C4 is the Score (a percentage)
AD13 is the operator
AC13 is the value to compare with

I have tried ="=IF("&C4&AD13&AC13&",0,2)" which I thought would work, but that way it is just shown as text.

I have also tried =IF(C4&AD13&AC13,0,2) but that just shows a #VALUE! error..

Your help would be appreciated!

Thanks

**mehmetcik** · 12-06-2014, 03:23 PM

I am confused.

Is this for a Macro or for a formula.

If this is a formula then:

=IF(AD13="<",IF(C4<AC13,0,2),"") & IF(AD13=">",IF(C4>AC13,0,2),"")

**ELeGault** · 12-07-2014, 05:17 AM

Why are we not using the CODE in this thread?

That # is to wrap your data in code tags so that it is clear and separate from the verbiage on the sheet.

If MehMetcik resolved your issue be sure to set as Solved through Thread Tools near the top.

Explanation of why your attempts are failing.

The IF AND statement is done through the word AND, the use of an Amperes symbol & is to combine or concatenate things together. SO

Please Login or Register  to view this content.

would result in

YesNo

.

So to begin with, that was where you initially went wrong.

For IF AND you want to use this syntax

=IF(AND(LOGIC Statement 1,Logic Statement 2, Logic Statement 3, etc..),ACTION When True, ACTION When False)

Each logic statement is a standalone check, but all of the checks you do within the And statement must pass in order for your TRUE to take effect, otherwise if any fail it goes to False.

Now you can do the same thing with an OR, Same format only replace AND with OR. This will make it so that as long as Any of the logic statements you put in pass, TRUE will take effect.

You can have as many logic statements as you see fit hence the etc... just stick to that format and you will be good to write your own formula!

To end this - Please share more than cell references so we can further understand where you are going with this. That will only help us help you...and if possible post a test book with a before and after sheet showing where you start, and the results that you expect to achieve!

Enjoy