Let’s discuss some popular functions from every category of functions there are. But before we do that let’s discuss about Series which is not a formula or function as such but is a very handy shortcut.
Series: A series is a collection of sequentially arranged entries in adjacent cells, such as the numbers 1 to n or the weekdays or even the months of the year for that matter.
The fill approach may be used to build these for you in a spreadsheet, decreasing the amount of effort spent on data entering and assuring perfect spelling. Days and months are available as unique built-in series in Excel.
There are various excel tutorial videos are worth watching to gain knowledge on functions as they are the backbone of any Excel job. However, we have tried to not leave even one stone unturned. With that let’s dive right in.
Math and Trigonometry Functions
1) SUM: The SUM function sums up the values from a selection of rows and columns. It is written as.
=SUM (number1, number2, …). For example,
=SUM (B3:G3): Returns the sum of values of a row.
=SUM (A2:A7): Returns the sum of values of a column.
=SUM (A2:A6, A9, A11:A14): A complex collection that returns the sum of values from range A2 to A6, skips A7 and A8, adds A9, skips A10 and finally adds through A11 to A14.
2) SUMIF: SUMIF adds all cells that match certain criteria. It is written as.
=SUMIF (range, criteria)
Top IT Risk Management StrategiesThe Range argument specifies the range of cells we want to test the criteria against. Criteria is the condition we want to check depending on which the values in the specified cells will be added. You may want to watch an excel video for this function as it is quite an important function.
3) SQRT: SQRT method is used to find the square root of a number. It is written as.
=SQRT (number).
4) CEILING: CEILING function rounds up a number to the nearest multiple of the significance argument away from zero. It is written as.
=CEILING (number, significance).
For example, =CEILING (10,3) would give us 12 as it rounds up 10 to the nearest multiple of 3.
5) SUMPRODUCT: The SUMPRODUCT function first multiplies the corresponding array components and then subsequently add them. It is a popular function in the different finance related jobs as it is analogous to weighted average. It is written in the form.
=SUMPRODUCT (array1, [array2], [array3],)
where the first argument is mandatory and the subsequent ones are optional.
Statistical Functions
1) AVERAGE: The AVERAGE function returns the simple average of the data. It is written as.
=AVERAGE (number1, number2, …). For example:
=AVERAGE (B2:B11): Returns the simple average of number values in the range B2 to B11.
2) COUNT: The COUNT function returns the number of numeric values in a range of cells. It is written as.
=COUNT (number1, number2, …). For example,
=COUNT (A: A): Returns all the values that are numerical in A column.
=COUNT (B1:G1): Returns all the values that are numerical in the first row from column B to G.
3) COUNTIF: COUNTIF counts all cells that fulfil specified requirements, whereas assume you wish to count all the cells that are more than or equal to 18 (the legal voting age in the India) to determine how many legal voters there are in case of an election. COUNTIF would be the perfect function to use in such a case. The way to write the function is as mentioned below.
=COUNTIF (D5:D12,”>=18″).
4) LARGE: LARGE function returns the nth largest value in an array of cells. Its syntactical usage is as.
=LARGE (array, k).
For example, =LARGE (B2:B10, 2) would return the second largest element in the range of B2 to B10.
5) MAX & MIN: The MAX and MIN functions return the maximum and the minimum values from a range of cells respectively. The MAX function is written as.
=MAX (number1,number2, …).
For the MIN function it is:
=MIN (number1, number2, …). For example,
=MIN (B3:C10): Returns the minimum value in the range B3 to C10.
=MAX (B3:C10): Returns the maximum value in the range B3 to C10.
