Excel formulas - abdullahbintahir/Python-Snippet GitHub Wiki

Approval Threshold & Hierarchy Control Joint

=LET(Amount,$H:$H,Approver_Designation,$F:$F,Submitter_Designation,$D:$D,
IF(AND(@Amount>500000,OR(@Approver_Designation={"Equity Partner","Income & Associate Partner"})),"Threshold Issue",
IF(AND(@Amount>100000,@Approver_Designation="Director"),"Threshold Issue",
IF(AND(@Amount>20000,OR(@Approver_Designation={"Senior Manager","Senior Advisor"})),"Threshold Issue",
IF(AND(@Amount>1000,@Approver_Designation="Manager"),"Threshold Issue",IF(@Approver_Designation="","",
IF(AND(OR(@Submitter_Designation={"Equity Partner","Income & Associate Partner"}),OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Ghost Account"})),"",
IF(AND(@Submitter_Designation="Director",OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Ghost Account"})),"",
IF(AND(OR(@Submitter_Designation={"Senior Manager","Senior Advisor"}),OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Director","Ghost Account"})),"",
IF(AND(@Submitter_Designation="Manager",OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Director","Senior Manager","Ghost Account"})),"",
IF(AND(OR(@Submitter_Designation={"Senior Advisor","Analyst","Senior","Consultant","Assistant Manager","Staff"}),OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Director","Senior Manager","Senior Advisor","Manager","Ghost Account"})),"","Hierarchy Issue")))))))))))

Approval Threshold

=LET(Amount,$H:$H,Approver_Designation,$F:$F,Submitter_Designation,$D:$D,
IF(AND(@Amount>500000,OR(@Approver_Designation={"Equity Partner","Income & Associate Partner"})), "Threshold Issue",
IF(AND(@Amount>100000,@Approver_Designation="Director"), "Threshold Issue", 
IF(AND(@Amount>20000,OR(@Approver_Designation={"Senior Manager","Senior Advisor"})), "Threshold Issue",
IF(AND(@Amount>1000,@Approver_Designation="Manager"), "Threshold Issue", "")))))

Hierarchy Control

=LET(Amount,$H:$H,Approver_Designation,$F:$F,Submitter_Designation,$D:$D,
IF(@Approver_Designation="(blank)","",
IF(AND(OR(@Submitter_Designation={"Equity Partner","Income & Associate Partner"}),OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Ghost Account"})),"",
IF(AND(@Submitter_Designation="Director",OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Ghost Account"})),"",
IF(AND(OR(@Submitter_Designation={"Senior Manager","Senior Advisor"}),OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Director","Ghost Account"})),"",
IF(AND(@Submitter_Designation="Manager",OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Director","Senior Manager","Ghost Account"})),"",
IF(AND(OR(@Submitter_Designation={"Senior Advisor","Analyst","Senior","Consultant","Assistant Manager","Staff"}),OR(@Approver_Designation={"Equity Partner","Income & Associate Partner","Director","Senior Manager","Senior Advisor","Manager","Ghost Account"})),"","Hierarchy Issue")))))))

Count number of instances of a word in a cell

=(LEN(D2)-LEN(SUBSTITUTE(D2,"Addendum","")))/LEN("Addendum")

Categorization/ search a list of values in a string

=INDEX('Category Lookup'!B:B, MATCH(1, --ISNUMBER(SEARCH('Category Lookup'!A:A, L2)), 0))

Count number of people who are duplicates

=SUMPRODUCT((COUNTIF($B$2:$B$100, $B$2:$B$100)>1)/COUNTIF($B$2:$B$100, $B$2:$B$100&""))

Brackets

=LET(ApprovedAmount,SUMIFS(N:N,J:J,J2),
IF(AND(ApprovedAmount>=0,ApprovedAmount<=100),"$0-$100",
IF(AND(ApprovedAmount>=100,ApprovedAmount<=500),"$100-$500",
IF(AND(ApprovedAmount>=500,ApprovedAmount<=1000),"$500-$1000",
IF(AND(ApprovedAmount>=1000,ApprovedAmount<=1500),"$1000-$1500",
IF(AND(ApprovedAmount>=1500,ApprovedAmount<=2000),"$1500-$2000",
IF(AND(ApprovedAmount>=2000,ApprovedAmount<=3000),"$2000-$3000",
IF(AND(ApprovedAmount>=3000,ApprovedAmount<=4000),"$3000-$4000",
IF(AND(ApprovedAmount>=4000,ApprovedAmount<=5000),"$4000-$5000",
IF(AND(ApprovedAmount>=5000,ApprovedAmount<=10000),"$5000-$10,000",
IF(ApprovedAmount<0,"<$0")
))))))))))

Unique & Sorted List from paragraphs

=UNIQUE(SORT(TOCOL(TRIM(TEXTSPLIT(TEXTJOIN("*",,F3:F8),"; ","*")))))