(A STEP BY STEP GUIDE ON THE BASIC MICROSOFT EXCEL FUNCTIONS AND THEIR FORMULAR AND THEIR APPLICATIONS IN DATA ANALYSIS)
Every data analysis must know how to use the functions and formulars in Microsoft Excel. This is because they are very important when working with data, performing calculations, and solving data-relating problems. This guide will guide us on the basic functions like SUM, IF, IFS, AVERAGE and XLOOKUP, the best way to use them and how to solve problems with this functions.
INTODUCTION TO MICROSFT EXCEL FORMULAS AND THEIR FUNCTIONS
Generally, excel functions as software for processing numbers, but in details this software is also used to create, edit, sort, analyze and summarize data. In addition, Microsoft excel also functions to perform arithmetic and statistical calculations so that it can help in solving logical and math problems.
SUM,IF, IFS, AVERAGE, VLOOKUP AND XLOOKUP
SUM
This sum function is also known as addition. The sum functions help users add the total of any selected range of cell values on Microsoft excel. The SUM functions add values. You can add individual values, cell references or ranges or a mix of all three.
For example
=SUM(B2:B5) Adds the value in B2:B5
using that formular, the total as seen above is 325
=SUM(B2:B5), (C2:C5) Adds the cells in cell B2:B5 as well as cell C2:C5
using that formular, the total of both cell
as seen above is 665
IF FUNCTION
The if function is one of the most popular function in excel, it allows you make logical comparisions between a value and what you expected.
so if statement can have two results. the first result is if your comparision is true, and the second is if the comparision is false.
For example
using the excel function above to return "EXCELLENT" IF test score is greater than 75 and "GOOD" if it is not.
IFS FUNCTION
the ifs function is use to check whether one or more conditions are met and return a value that corresponds to the first TRUE condition.
for example
using the above function to return EXCELLENT IFS the test score os greater than 80.
XLOOKUP/VLOOKUP
VLOOKUP forces the user to indicate the entire table array searching for the look up value exclusively in the leftmost column. the column to return a value from is determine by the column number in the subsequent argument. XLOOKUP, in contrast, allows the lookup and return columns to be specified seperately. Xlookup functions searches a range or an array, and then returns the item corresponding to the first match it finds. if no match exists then Xlookup can return the closest (approximate) match. if omitted Xlookup returns blanks cells it find in lookup-array.
Vlookup has 4 argument while Xlookup has 6 argument.
XLOOKUP FORMULAR
Xlookup(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]
The first 3 arguments are required and the last three are optional.
- lookup_value: it is the value to search for
- lookup_array: The range or array where to search.
- Return_array: The range or array from which to return values.
- If_not_found {Optional}: The value of return if no match is found if omitted, an #N/A error is returned.
Match_mode: The match type to perform:
0 or omitted (default)- exact match. If not found, an #N/A error is returned.
-1 -exact match or next smaller. If an exact match is not found, the next smaller value is returned.
1 -exact match or next large. if an exact match is not found, the next larger value is returned.
2 -wildcard character match.
Search_mode {Optional}: the direction of search.
1 or omitted(default) - To search from first to last.
-1 - To search in reverse order, from last to first.
2 - Binary search on data sorted ascending.
-2 - Binary search on data sorted descending.
EXAMPLE OF BASIC XLOOKUP
where =XLOOKUP(D2,C2:C7,B2:B7)
EXAMPLE OF HORIZONTAL XLOOK UP
where =XLOOKUP(B5,B2:G2,B1:G1)
EXAMPLE OF ACROSS SHEET XLOOKUP
where the lookup array is taken from the basic lookup sheet
=XLOOKUP(A2,'BASIC XLOOKP'!C2:C7,'BASIC XLOOKUP'!B2:B7)
EXAMPLE OF XLOOKUP USING THE PRICE TABLE
where the price and cost of chocolate chip =XLOOKUP(D2,C2:C7,A2:B7)
EXAMPLE OF MULTIPLE XLOOKUP
where =XLOOKUP(E2&F2,B2:B7&C2:C7,A2:A7)
EXAMPLE OF XLOOKUP USING MATCH MODE
where =XLOOKUP(D2,A2:A7,B2:b7,,-1)
EXAMPLE OF XLOOKUP USING SEARCH MOOD
where =XLOOKUP(D3,A1:A16,B1:B16,,,-1)
EXAMPLE OF NESTED XLOOKUP
where =XLOOKUP(B4,B8:D8,B9:D14)
AVERAGE
What is Average
In everyday life, the average is a number expressing the typical value in a dataset of a data. In mathematical terms, the average is the middle or central value in a set of number, which is calculated by dividing the sum of all the values by their numbers. the function calculate the average of any choosen range of cells.
Top comments (1)
An insightful Article