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".
[Proposed % discount]
column? Can you please attach the screenshot of "column settings" for this column (if possible)? This will help us to understand how data is stored in[Proposed % discount]
.