1

I would like to use an IF function to obtain an answer to "Approve" or "Decline" within a SharePoint list. My formula works in Excel but not in SharePoint.

I have 4 SharePoint columns:

column name = [Request Type], excel cell =G2 (to be "approved" the word "TD" must be chosen)

column name = [Position title], excel cell =F2 (to be "approved" the word "MEM" must be chosen)

column name = [% Discount], excel cell =J2 (to be "approved" value must be <=0.6)

column name = [Criteria Total], excel cell =S2 (to be "approved" value must be >=100)

My formula works in Excel but not in SharePoint. Excel formula is: =IF(AND(G2="TD",F2="MEM",S2>=100,J2<0.7),"APPROVE","DECLINE")

When I enter it in SharePoint I change the cells to the column name and it always says "decline" when the answer should be "approve"

Can someone please help me?

2
  • What is the data type of [% Discount] and [Criteria Total] columns in SharePoint list? Feb 3, 2021 at 6:13
  • [% Discount] = choice of values ranging from 0.1 through to 2.0 (increments of 0.1) [Criteria Total] = calculated column (=sum) adding up 3 column values
    – Kelley
    Feb 3, 2021 at 6:16

2 Answers 2

0

Assuming [Criteria Total] has returned data type as Number, you can use below formula:

=IF(AND([Request Type] = "TD", [Position title] = "MEM", [Criteria Total] >= 100, Value([% Discount]) <= 0.6), "APPROVE", "DECLINE")

Microsoft documentations:

  1. AND function
  2. VALUE function
1
  • 1
    Ganesh, you are wonderful.. it works... thank you so very much.. Have a wonderful rest of the week :-)
    – Kelley
    Feb 3, 2021 at 6:28
0
=IF(AND([Request Type]="TD",[Position title]="MEM",[Criteria Total]>=100,[% Discount]<0.7),"APPROVE","DECLINE")

Test result: enter image description here enter image description here Updated:

=IF(AND([Request Type]="TD",[Position title]="MEM",[Criteria Total]>=100,VALUE([% Discount])<0.7),"APPROVE","DECLINE")

enter image description here enter image description here Because the original value of % Discount is a text value, it needs to be converted to a number. VALUE function

1
  • it has not worked - wondering if thats because % discount is a choice field starting at 0.1 (increments of 0.1) all the way to 2.0. Also Criteria Total is also a calculated total (=sum) adding up 3 columns
    – Kelley
    Feb 3, 2021 at 6:19

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.