The key to understanding formulas is that they start with the "=" sign.
I think this is important because as a beginner I always forget this and start wondering why my formulas aren't working. Keeping this in mind will definitely save you a lot of time.
officiallyIn Excel, it is a word or statement that, when executed, performs a specific action on data (input) and produces an output.
If you want to go a little deeper, formulas are defined as Excel functions, and each formula performs a specific task based on how it is defined. You pass data through a function as input and get the desired output based on the types you use.
You can use the formula,summary data,get the maximum value from an array of data,formatted dataI am waiting. The entries used in the formula are atparentheses ().The value in parentheses is passed through the function and output.
There are some commonly used Excel formulas such as:
minimum and maximum
Average and maximum formula share a syntax. They take an array of data as input and the maximum formula returns the maximum number in the array. As you can imagine, the min function outputs the smallest value in the array. The syntax looks like:
\=max(range)
\= minimum (range)
Examples of ranges might be H2:H6, A1:D10, K17:P87, etc. The first example indicates the range from cell H2 to cell H6.
It's me
IF allows you to define conditions based on a logical test of the data entered as input. The syntax is generally as follows:
\=IF (logical check, (value if true), (value if false))
A type usually checks whether the logical test is true, and if it is, it returns the specified output, and if not, it returns the output according to the given command. A logic test includes a series of data that will be compared to the logic test.
\=IF(H2:J15>15, "Above_threshold","Below_threshold")
This will check if the data in the range is greater than 15. For any value greater than 15, excel will print "Over_threshhold" in the cell and for any value less than 15, excel will print "Below_threshhold" in the corresponding cell
IFS allows you to apply multiple conditions and differs from If in that it does not contain a value if the selected condition is false. The syntax will look like this:
\=IFS(logical test 1, value if true, logical test 2, value if true)
\=IFS(Range = "Teacher", "Workgroup F", "Principal", "Workgroup")
length (length)
Functions that can be used to check the length of a string. It can be used to check phone numbers, social security numbers to check for missing values. The len function returns the length of the string in the selected cell. The syntax will look like:
\=LEN(A5)
This will return the length of characters in the cell.
eye
These functions select specific parts of text strings and extract data from them. In the formula, you specify the number of characters to print from the beginning of the string. Using the left function will start counting strings from the left and using the right function will start counting strings from the right.
Assuming I have a use case where the date format is dd-mm-yyyy, I have the option to use the correct function to select the year from a range of cells using a formula:
\=desno(B25:B34, 4)
B25:B34 represents the range of cells we want the formula to affect, and 4 represents the number of characters to select in the output. In this case, uhh.
\=Left(B25:B34,2)
This will only print dates from the given range by selecting dd
text date
As it says, the formula is for converting a date to a text data type. The left/right formulas listed above only work with array data types. Therefore, you will need to use the date-to-text type to convert the date data type to a string data type. The syntax will look like:
\=TEKST(G5:P5, "dd-mm-gggg")
G5:P5 specifies the range of cells that require changes to be applied. After that, we need to specify the format in which the date already exists in the formula.
aid:To determine whether the input value is a date type or a string type, the date type uses right indentation in the cell, while the string type shows left indentation in the cell.
circumcise
This is a basic function that removes unwanted spaces from either side of text or data. This makes the data easier to read. The syntax is similar to:
\=scrati(C2:C7)
in a row
A formula that concatenates two or more text strings into a single text string. Only works with text/string data types. Linked text is usually in different cells. grammar:
\= join (D9," ", E9)
"" creates a space between two strings. You can put any character between the quotes and it will be included in the output.
This formula can be used to compose an email. It will appear as:
replace
As the name suggests, it is used to replace values or characters with specified characters. Replaces existing text with new text in a text string.
You probably have a date in the formatdd-gggg-mmand you want to convert it today/year/month.The syntax is similar to:
\=REPLACE(D2:H5, "-", "/")
The formula also allows for the number of cases that have changed to be included. If you do not enclose occurrences in parentheses, the type will change where the character occurs.
There is an example:
\=REPLACE(D2:H5,”-”,”/”, 1)
The date format will be printed as: dd/yyyy-mm. The formula will only change the first occurrence of "-".
There are two cases:
\= Replace(D2:H5,"-", "/", 2)
The date format will be printed as dd-yyy/mm. The formula will change the second occurrence of "-".
Sum i Sumif
The sum totals all the values in the selected range of cells.
\=SUM(H2:H15)
This will sum all the values in the selected range.
Sumif
Adding an if to a formula creates a condition where the values are added if they meet the condition you set.
\=SUMIF(A4:A10,>3000")
This will sum the numbers in the range that meet the specified criteria.
Abstract
Sumifs allow users to define multiple conditions when calculating sums. They sum the values in the range that meet all the specified criteria.
\=SUMIFS(G2:G6, R4:R16,"Ženski", D4:D8,">30")
Before summing, the function checks if the values between R4 and R16 are female, and then continues to check if the other conditions are true before summing.
count
This gives a count of how many cells are in a given area.
\=number(G5:H7)
count
This counts cells that meet certain criteria.
\=COUNTIF(J4:K7,”>5000”)
This will give a number of entries greater than 5000 in the range J4 to K7.
count
This will allow multiple conditions to be checked before counting cells in a given range.
\=COUNTIF(G5:H17,">5000", E34:E45,"Muški")
This checks for values greater than 5000 in the range G5:H17 and cells with males in the range E34 to E45.
the
The Days formula calculates the number of days between dates in a cell.
\=DAYS (enddate, startdate)
cyber and
Similar to the Days type, but removes weekends and holidays, leaving the number of business days between dates.
\=NETWORKDAYS(end_date, start_date, [holiday])
Xlookup
This type searches for a match in a range or array and returns the corresponding element from another range.
An array is a range.
The pension is:
\=XLOOKUP (lookup value, lookup field, return field)
The Look_up value highlights the cells with the lookup subject value.
The Look_up array is the range of cells for which the lookup values will be checked.
The return_array is the array of return values to check and match the search values. You can select multiple rows for the return value, so all selected columns will generate their own data output based on the corresponding lookup value.
Xlookup_exact match— This allows the return values to be printed if the lookup value is not found in the lookup table.
\=XLOOKUP(A10, T5:T36, G4:G7,"not found")
If the lookup value is not found, a not found output will be returned.
Wildcards on Xlookup.
Suppose the user does not know all the characters or words in the search value. They won't be able to determine the exact price they want to look for.
To solve this problem, excel offers a wildcard option, which is defined by special characters between quotation marks (*). Place an ampersand (&) between the wildcard and the known word.
Wildcards are placed before or after words that we know are in the search value. If an unknown word appears before a known word, place a wildcard in place of the unknown value before the known word.
If an unknown word appears after a known word, it is placed after the known word. A wildcard specifies a value, it just doesn't say what value can take its place. example:
XLOOKUP("*"&A4, H2:H10, O2:O10,"Not Found")