Top 20 MS Excel Formulas and Function: Learn with Example
- To use the various computational features of MS Excel, there are a number of basic formulae and functions that you must be aware of. Click here to lea
To use the various computational features of MS Excel, there are a number of basic formulae and functions that you must be aware of. Click here to learn with example.…
Basic Formulae and Functions of M.S Excel: Know the Difference
MS Excel has 2 main facilities for running mathematical operations on data. These include formulae and functions, which are closely related to each other but have one fundamental difference between each other: A formula in Excel is a command for a mathematical operation that you feed into Excel. An example of a formula would be “=A4+C4” A function is a predefined command for a mathematical operation in Excel which has a set syntax and format. A simple function in Excel is the SUM function used as “=SUM (A2:B2)” Notice that one needs to manually feed the info about the cells to be added in the case of a formula (you will need to feed the data about 5 cells manually if you wish to add those 5 cells) while in the case of a function, you can use the SUM function directly and just enter the values of the cell (e.g. A2 to E2 represented as A2:E2) for the software to automatically calculate the sum of the cell values. Functions are easier to use as compared to formulae in Excel since they come with pre-designed syntax and can directly be used to perform various operations with the data. Another major advantage of using a function in Excel is that there are many operations and commands for which it is not possible to directly feed a formula manually to the software. In such cases, there are many functions of Excel which can be used to directly run the command.
Formulae and Functions: Syntax of a Formula and Function
Before proceeding onto using the actual formulae and functions in Excel, one needs to have a basic understanding of what is the structure of a formula or function, which is referred to as the “syntax”. For a formula, the syntax includes an equal sign (“=”) followed by the operation you wish to perform along with the relevant cell references. For example if you need to find the product between cells A1 and D5, we can create a formula as follows: =A1*D5 For a function for the same operation, we shall begin with the equal sign (“=”) followed by the function command we wish to perform (e.g. PRODUCT in our case), followed by a bracket wherein the cell references are provided in which the function is to be performed. So for the above example, the function to use is PRODUCT and the syntax will be: =PRODUCT(A1,B2) Here, since we are already providing the function PRODUCT outside the brackets, we do not enter an asterisk (“*”) sign rather we separate the two cell values through a simple comma (“,”). For some functions, instead of the comma (,) one needs to use the colon (“:”) instead which denotes a series of cells rather than a couple of cells on which the operation is to be performed. Given below is an easy tutorial on how to use the various functions and formulae in MS Excel along with the use cases for those functions.
1) SUM Function
This is the simplest function of the software and it is used to calculate the additive sum of the cells you feed into the command. The function command begins with an equal sign (“=”) followed by the main command ( “SUM”), followed by the cells whose values are to be added. You can enter the cell command in terms of specific cells (separated by a comma) or a series of cells (separated by a colon- “:”). The command is: =SUM(starting cell:ending cell) Enter the command- =SUM( Now select the cells you wish to add, these will be visible within the brackets of your command Close the command with a bracket -“)” Press Enter The sum of the reference cell values will be displayed in the cell.
2) SUMIF Function
The SUMIF function is an extension of the SUM function, in which we add a condition about the sum, for example-only adding cell values when they exceed a particular value. This function is useful for screening particular values from a dataset and performing the addition only on specific cell values. The syntax of this command is similar to the SUM command, but includes an additional subcommand about the condition you want to place on the cell values. It starts with an equal sign (=) followed by the main command- SUMIF This is followed by a bracket within which you enter the cell values (e.g. A2:F2) After entering the cell values, put a comma (“,”), followed by your condition (e.g >5) in double inverted commas. The command closes with the bracket. So the final syntax of the command is- =SUMIF(start cell: end cell, “Condition”) In the given dataset, if you want to find out the sum of scores for students who have scored more than 5 marks in the mathematics exam, then you can use the command =SUMIF(B2:B7, “>5”).
🎓 Unlock Up To 100% Merit Scholarships for 2026 Admissions
Apply via EduXTech Common Form & get direct counseling from top NAAC A++ Universities.