How we can use functions in Microsoft Excel
Certifications:
- How we enable Unified Auditing in Oracle Database - 7 October 2024
- What is PostgreSQL and how do we do a full installation - September 2, 2024
- How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024
How can we make use of the most useful Microsoft Excel functions?
In this article we will analyze some of the most basic and most useful functions and how to use them correctly. Through the functions we can perform mathematical calculations, combine data and format them. The functions we will analyze are sum , if , count, iferror and vlookup.
By selecting the button fx we will be shown the graphical environment where we can find all the functions provided.
Let's start with the simplest.
Addition (SUM / SUMIfs)
We can perform addition on a range of cells (SUM) or to perform addition to a range of cells that meet some criteria.
Example
Adding the text to the cell =SUM(E2:E8) cell E2 through E8 will be added. If we drag the cell or paste it to another point, it will make a corresponding addition depending on what the cells contain in the column and row where we placed it.
Yes but we copied the cell from position E9 to F9 and it showed us zero why?
You'll notice in the previous image that the function has automatically changed to =SUM(F2:F8). If we always wanted it to do the operation for cells E2 to E8 we would have to lock it. To lock we add the $ in front of the coordinate we want you not to change, in this particular case it would be enough =SUM($E2:$E8), but in order to be able to save the cell in a different column and in a different line, it would be safer to lock it as =SUM($E$2:$E$8).
Case (IFs)
We can add a criterion to display a result in case it meets one condition (IF) or several different ones (Ifs).
Example of Ifs
=IFS(C2="Stratos","Γιορτάζει 13 Δεκεμβρίου",C2="Maria","Γιορτάζει 15 Αυγούστου")
For greater convenience, by selecting the function button, we can complete the criteria with a graphic:
But we see something #N/A what are these;
#N/A means not available and it means that no data was found with these criteria, the solution is in the next function.
If you find an error (IFERROR)
We can combine the IFERROR function to eliminate error messages such as #N/A with other functions that may be contained within it.
Example:
=IFERROR(IFS(C2="Stratos","Γιορτάζει 13 Δεκεμβρίου",C2="Maria","Γιορτάζει 15 Αυγούστου"),"")
Count (Count / CountIfs)
With the count function we can count the number of records (Count) or the number of records that meet specific CountIfs criteria.
Example.
We want to count how many records have the name Stratos.
=COUNTIF(C2:C8,"Stratos")
Find Values (VLOOKUP)
One of the most must functions, with the vlookup we can associate data bringing information that may be on the same sheet or different as even in a different Excel file.
Example.
We want to use the "Product Code" to fetch its name from the "Products" sheet.
Calling the function we should declare it Sheet(sheet) where the data is located (along with an exclamation point "!"), the field that will be the criterion and the table that has the values we want to fetch. At this point we must pay attention to Table_array the initial column should be the field we use as a key and the information we want to bring should be next. In this example the key is in the Products tab in column A and the name information in B (second column), so in the field col_index_num we will declare 2 that we want the second column (if we wanted the cost we would put 3 etc.). End of Range_lookup we put false so that it will bring us only if it finds its exact value.
(in this particular case because we want to drag the field down and apply it separately for each line we should not lock the fields with $)
=VLOOKUP(Sales!B2,Products!A:C,2,FALSE)
In closing, let me give you a tip
In an old interview I was given a test on Excel functions, although I had completed all of them, some did not give me the result and I was at a loss to understand why.
It can be seen in the example that I have filled in the function that we had put in cell E9, but it doesn't work, why?
The problem lies in the type of the field. In order for a function to be executed, it must have a type field General.
Simple and understandable everything!