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? – Ganesh Sanap Feb 3 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 at 6:16
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 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 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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