Advance Functions — Microsoft Excel
In this article, we will delve into the concepts of advance functions in microsoft excel and along with that we will also learn how we can use it and how it help us to reach our desired results in accounting and finance world.
Let’s Dive In
Advance Functions
Advanced Excel Functions refer to the features and functions of Microsoft Excel that help users perform complex calculations, data analysis, and much more. We will use the most common advanced functions in our journey of learning Excel for Accounting and Finance.
- Sumif and Sumifs
- Count and Average Functions
- Date Functions
Sumif and Sumifs
Both functions are used to sum a range with a related condition. To differentiate, the SUMIF function allows for imposing a single condition on our data, while the
‘SUMIFS’ function allows for setting multiple conditions.
The basic syntax of SUMIF and SUMIFS functions is as follows:
- =SUMIF(range, criteria, [sum_range]).
- =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2],
[criteria2],…).
Here, we have some salespersons’ name, their selling products, and total pieces.
We are going to find the total pieces sold by Vishal:
=SUMIF(B2:B16,"Vishal",D2:D16)
We will get 28.
Now, we are going to find the total number of TVs sold by Vishal in Delhi Zone:
=SUMIFS(D2:D16,B2:B16,"Vishal",C2:C16,"TV",A2:A16,"Delhi")
We will get 4.
Count and Average Functions
We will learn about the Count and Average functions in Excel.
Time Functions
Time functions are very useful for finance and accounting professionals. All projections and estimations depend on time for finance and accounting professionals and we will learn the time and date functions and
their uses.
- EDATE
- EOMONTH
- WEEKNUM
- YEARFRAC
Conclusion
In this guide, we will understanding the financial functions and different formulas that is very useful for making equations that help us get to know about company and organization financial health and it also frequently used by finance professionals around the globe.
