Calculated column with IF(AND(OR statement to derive a value

Question

I would like to use an IF(OR(AND function (or a function that works) to obtain an answer of 1 or -100 within a SharePoint list. My formula works in Excel but not in SharePoint.

=IF(OR(AND(Q10="NO",P10<=0.1),AND(Q10="YES",P10<=0.2)),1,-100)

Discount = 0.2
LVR<70% = yes
(RESULT WILL EQUAL 1)

If LVR is <70%, then the maximum discount can be up to 0.2 (ranging in value from 0.01 through to 0.2 with intervals of 0.01)

Discount = 0.1
LVR<70% = no
(RESULT WILL EQUAL -100)

If LVR is NOT <70%, then the maximum discount can be up to 0.1 (ranging in value from 0.01 through to 0.1 with intervals of 0.01) This works in excel.

When I move it over to the calculated column in SharePoint:

=IF(OR(AND([LVR<70%]="NO",[Proposed % discount]<=0.1),AND([LVR<70%]="YES",[Proposed % discount]<=0.2)),1,-100)

SharePoint accepts it but doesn't calculate the correct answer. (always gives -100 as the answer).

The calculated column is "The data type returned from this formula is: Single line of text".

Answer 1

I've seen very bad things happening when special characters are involved in a column name. Using < and % in a column name is a really bad idea.

But that's not the cause of your issue. You never mention the data type of the column [LVR<70%] but since you test against "YES" and "NO" I assume it's a Yes/No field.

While SharePoint displays the values as yes and no, internally they are stored as Boolean TRUE/FALSE values. So, in order to evaluate them, you only need the field name. It returns either TRUE or FALSE.

So, you need to rework you formula to use the column name as a boolean, like this

=IF(OR(AND(not([LVR<70%]),[Proposed % discount]<=0.1),AND([LVR<70%],[Proposed % discount]<=0.2)),1,-100)

Finally a screenshot with the more complex formula at work, just using different thresholds:

edit 2: formula at work with OP's settings