Geeks on Campus

anything, everything, geeky.

Excel Formulas

Posted by jeggent on August 20, 2020

Flag if one of two strings is found in a cell.

=IF(AND(ISERROR(FIND(“fall 2020”,AB2)),ISERROR(FIND(“Fall 2020″,AB2))),”N”,”Y”)

IFS, Switch, Min
It was an Excel formula before it was a PS Query Expression

=IFS(X3>0,0,W3>0,SWITCH(V3,”NO NEED”,250,”HIGH”,500,”MODERATE”,500,”LOW”,500,”NO FAFSA”,0,0),W3<1,SWITCH(V3,”HIGH”,MIN(1200,S3),”MODERATE”,MIN(1000,S3),”LOW”,MIN(750,S3),”NO NEED”,MIN(500,S3),0)) 

Convert number (Student ID) into text to replace and keep leading zeros
=TEXT(C2,”0000000000″)

Does a cell contain one or more of these given strings
=IF(OR(ISNUMBER(SEARCH(“2021”,D3)),ISNUMBER(SEARCH(“2020”,D3)),ISNUMBER(SEARCH(“2019″,D3))),”Yes”,”No”)

Explained: SEARCH returns a number indicating which character position your given string (“2021”) starts at in the target cell (D3). If the string is not found it returns #N/A. So ISNUMBER is used to return True if SEARCH returns a number (and False if #N/A). Three of the ISNUMBER SEARCH pairs are grouped with the OR so that if any of them is True than True is returned to the IF statement.