EXCEL Advanced functions and formulas (PART-II)

1. The COUNTIF function will count the number of cells within a range that meets the criteria.

For Example, =COUNTIF(E:E, K2)

Similarly, SUMIF and AVERAGEIF will give the sum and average respectively within a range that meets the criteria.

2. The COUNTIFs, SUMIFs, AVERAGEIFs, MAXIFs, and MINIFs function are the plural version, where you can specify multiple criteria.

For Example, =COUNTIFS(E:E,K2,H:H,5)

=AVERAGEIFS(G:G,E:E,K2,D:D,”>9")

3. The RANK.EQ function rank the number against the range of numbers.

For Example, =RANK.EQ(G2, G:G)

4. The LARGE function can retrieve the “nth largest values”

For Example, =LARGE(B:B,2) . This will retrieve the 2nd largest value.

5. The COUNTBLANK function returns a count of empty cells in a range.

For Example, =COUNTBLANK(B1:B100). This will count the blank in the range from 1 to 100.

6. The COUNT function returns the number of cells with numbers in them.

7. The COUNTA function returns the count of cells containing data.

Remember, COUNT returns the count of cells containing numerical data only,

COUNTA returns the count of cells containing data.

For Example, =COUNTA(D:D)

8. The ROUNDUP function rounds up a number to a given number of digits.

For Example, =ROUNDUP(A1,2)

9. The ROUNDDOWN function rounds the number down to a given number of digits.

For Example, =ROUNDDOWN(A2,2)

10. The MROUND function rounds the given number up or down to the specified multiple.

For Example, =MROUND(7, 2)

It will round to the nearest multiple of 2, the output will be 8.

10. The CEILING function rounds a given number up to the nearest multiple of significance.

For Example, =CEILING(2.5,2)

Here, it will round 2.5 to the nearest multiple of 2, which is 4.

11. The FLOOR function in Excel is used to round a given number down, to the nearest multiple of a specified significance.

For Example, =FLOOR(2.5,2) rounds 2.5 down to the nearest multiple of 2, which is 2.

12. The TRUNC function always drops the decimal point.

TRUNC(-2.5) = -2 , TRUNC(2.5) = 2

13. The INT function always returns the lower value.

INT(358.05) = 358 , INT(-46.75) = -46

14. The ODD function will return the nearest odd number moving away from zero.

ODD(22) = 23, ODD(-3.56) = -5

15. The EVEN function will return the nearest even number moving away from zero.

EVEN(22.01) =24, EVEN(-3.40) = -4

16. The RAND function will generate random number between 0 and 1.

=RAND() , 0.6677

17. The RANDBETWEEN function will generate a random number between 2 integers.

=RANDBETWEEN(10,50)

RANDBETWEEN calculates the random number each time the worksheet is calculated. To stop the random numbers from being updated, copy the RANDBETWEEN values, then use paste special — values to convert to text.

To refresh the random number, press f9 to regenerate the random number.

18. The RANDARRAY returns an array of random numbers.

=RANDARRAY(rows,cols)

=RANDARRAY(2,3), It will generate random numbers across 3 columns and 2 rows.

19. The CONVERT function will convert the value between measurement systems.

=CONVERT(37, “C”, “F”), it will convert 37-degree Celsius to Fahrenheit ie 98F

20. The AGGREGATE function returns an aggregate calculation like count, average, sum, etc., also we have the option to ignore hidden values or errors.

=AGGREGATE(9,6,D:D)

9 — SUM, 6 — Ignore error values, D:D — entire D column to be aggregated.

The above function will ignore the error values.