The key thing to know about formulas is that they begin with an “ = ” sign.
I feel like this is important to begin with because as a beginner I would always forget that, and start wondering why my formula is not working. Remembering this will definitely save you a lot of time.
Formulas in Excel are words or statements that when executed, perform a specific action on data(input) and give an output.
If you were to go a little in-depth, the formulas are defined as functions of Excel and each performs a specific task based on how it has been defined. You run data through the function as input and get a desired output based on the formula used.
You can use a formula, to sum up data, get the maximum value from an array of data, format data, etc. Input to be used in formulas is placed in parenthesis (). The values in parenthesis are run through the function, and output is given.
There are some commonly used Excel formulas which include:
Min and Max.
The mean and max formulas share syntax. They take a range of data as input and the max formula gives the maximum number from the array. As you would have thought, the min function gives the minimum value from the array as output. The syntax appears as:
\=MAX(range)
\=MIN(range)
An example of a range could be H2:H6, A1:D10, K17:P87, etc. The first example indicates a range from cell H2 to cell H6.
IF and IFS
IF allows you to place a condition based on a logical test on the data put in as input. The syntax generally is:
\=IF (logical test, (value if true), (value if false))
The formula generally checks if the logical test is true and if so, gives an output as defined, and if not gives output depending on the instructions given. The logical test includes a range of the data that will be checked against the logical test.
\=IF(H2:J15>15, “Over_threshhold”,”Below_threshhold”)
This will check for the data within the range whether or if it is greater than 15. For each value greater than 15, excel will print “Over_threshhold” in the cells and for each value lesser than 15, excel will print ”Below_threshhold” in their respective cells
IFS allows you to apply multiple conditions and is also different from If in that it does not include a value if the condition checked is false. The syntx would appear as follows:
\=IFS(logical test 1, value if true, logical test 2, value if true)
\=IFS(Range = “Teacher” , “Job group F”, “Principal”, “Job group E”)
Len(Length)
A function that can be used when checking the length of a string. It can be useful for checking phone numbers, social security numbers to check for those with missing values. A len function returns the length of the strings in the cells selected. The syntax will appear as:
\=LEN(A5)
This will return the length of the characters in the cells.
LeftRight
These functions choose a certain part of a text string and extract data from that. In the formula, you specify the number of characters you want to be outputted from the start of the string. Using the left function will count strings from the left while using the right function will count strings from the right.
Assuming a use case where I have dates with a date format dd-mm-yyyy, I can choose to select only the year from a range of cells using the right function by using a formula:
\=RIGHT(B25:B34, 4)
B25:B34 will denote the range of cells we want affected by the formula while 4, denotes the number of characters selected in the output. In this case, yyyy.
\=LEFT(B25:B34,2)
This will output the day only from the range given, by selecting dd
Date to Text
As it states, the formula is used to convert dates to text data types. Left / Right formulas mentioned above work with string data types only. You will thus be required to convert date data types to string data types using the Date to Text formula. The syntax will appear as:
\=TEXT(G5:P5, “dd-mm-yyyy”)
G5:P5 specifies the range of cells where we need the change effected. Thereafter in the formula we need to specify the format in which the date already is in.
A tip: to tell if an input value is a date type or a string type, date types have a right indentation in the cell while string types display a left indentation in the cell.
Trim
It is an essential function that removes unwanted spaces from both sides of the text or data. This makes the data more readable. The syntax will resemble:
\=TRIM(C2:C7)
CONCATENATE
A formula that joins two or more text strings into one text string. It is only applicable on text/string data types. The texts being joined are usually on different cells. The syntax:
\= CONCATENATE(D9,” “,E9)
The “ “ creates a space between the two strings. You can place any character between the quotation marks and the character will be included in the output.
This formula can be used to write emails. It would appear as:
Substitute
As the name suggests it is used to substitute values or characters with defined characters. It replaces existing text with new text in a text string.
You might have a date in the format dd-yyyy-mm and wish to convert it to dd/yyyy/mm. The syntax would resemble:
\=SUBSTITUTE(D2:H5, “-”, “/”)
The formula also allows to include the number of instances the change will take place. If you do not include an instance in the brackets, a formula will change the character wherever it appears.
With one instance:
\=SUBSTITUTE(D2:H5,”-”,”/”, 1)
The date format will be output as: dd/yyyy-mm. The formula will have changed only the first instance of “–“ that appears.
With two instances:
\= SUBSTITUTE(D2:H5,”-”,”/”, 2)
The date format will be output as dd-yyyy/mm. The formula will have changed the second instance where “-” appears.
Sum and Sumif
Sum adds up all the values in the range of cells that are selected.
\=SUM(H2:H15)
This will sum up all the values in the selected range.
Sumif
Adding if to the formula creates a condition where the values are summed up if they meet the condition placed.
\=SUMIF(A4:A10,”>3000”)
This will add up the figures within that range that meet the set criteria.
Sumifs
Sumifs allows the user to set multiple criteria when working out a sum. They will add up the values in the range that meet all the criteria defined.
\=SUMIFS(G2:G6, R4:R16,”female”, D4:D8,”>30”)
Before summing the function will check if a value between R4 to R16 is a female, then proceed to check if the other criteria is also true before summing up.
Count
This gives a count of how many cells there are within a certain range.
\=COUNT(G5:H7)
Countif
This does the count of the cells that meet certain given criteria.
\=COUNTIF(J4:K7,”>5000”)
This will give a count of the entries within the range of J4 to K7 that are greater than 5000.
Countifs
This will allow for multiple criteria to be checked before counting the cells within the range given.
\=COUNTIF(G5:H17,”>5000”, E34:E45,”Male”)
This will check for the values that are greater than 5000 within range G5:H17 and for cells that have males within ranges E34 to E45.
Days
The days formula counts the number of days between dates in cells.
\=DAYS(end_date,start_date)
Network days
Similar to days formula but eliminates weekends and holidsays, leaving a count of workdays between dates.
\=NETWORKDAYS(End_date,Start_date,[holidays])
Xlookup
This formula searches a range or an array for a match and returns the corresponding item from a second range.
An array is a range.
The syntax is:
\=XLOOKUP(look_up value, look_up array, return_array)
Look_up value highlights the cell with the value that is subject in the search.
Look_up array is the range of cells where the look_up value will be checked.
Return_array is the range that will be checked for the value that will be returned, correspondent with the look_up value. You can select multiple rows for the return value, thus all the columns selected will produce an output of their data each, in respect to the correspondent look_up value.
Xlookup_exact match — this allows for inclusivity of a return that would be output if the look_up value was not found in the look_up array.
\=XLOOKUP(A10, T5:T36, G4:G7,”Not found”)
If the look_up value is not found, an output not found will be returned.
Wildcard in Xlookup.
Assuming the user does not know all the characters or the words in the lookup value. They would not be able to specify the exact value that they want to be looked up.
To solve this, excel offers a wildcard option that is defined by a special character placed between quotation marks (*). An ampersand (&) is placed between the wild card and the known word.
A wildcard is placed before or after the word in the lookup value that we know. If the unknown word comes before the known word, the wildcard is placed before the known word in place of the unknown value.
If the unknown word comes after the known word, then it is placed after the known word. A wildcard specifies that a value, just not stated which value can occupy its place. Example:
XLOOKUP(“ * ”&A4, H2:H10, O2:O10,”Not found”)