0

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".

1
  • What is the data type and other settings of [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]. Feb 12, 2021 at 12:33

1 Answer 1

0

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.

enter image description here

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:

enter image description here

edit 2: formula at work with OP's settings

enter image description here

8
  • If that resolved your issue, please mark the answer as described in the tour. If it didn't, please leave a comment, so I can follow up.
    – teylyn
    Feb 12, 2021 at 2:37
  • Thank you for your assistance, however the result is still showing as -100 when the result should be 1. You are correct that the Yes/No are a Yes/No field. I have entered data of: LVR<70% = yes Proposed discount =0.12 with the answer returning -100, when it should be 1
    – Kelley
    Feb 12, 2021 at 2:51
  • Can you post the formula that you are now using? You can see in my screenshot that the approach works, so maybe there's something amiss with your formula.
    – teylyn
    Feb 12, 2021 at 3:11
  • I have taken your advice and removed special characters. Formula is: =IF(OR(AND(NOT([LVR 70]),[Proposed discount]<=0.1),AND([LVR 70],[Proposed discount]<=0.2)),1,-100)
    – Kelley
    Feb 12, 2021 at 3:12
  • Check your values in Proposed Discount. I'm using your exact formula and it works fine.
    – teylyn
    Feb 12, 2021 at 3:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.