Mathematical & Statistical Functions

SUM FUNCTION

Returns the sum of elements in a column.

The sum of non-contiguous cells

SUMIF FUNCTION

There are 3 kinds of cell references in Ms Excel.

1. Relative Reference 2) Absolute Reference 3) Mixed Reference

Relative Reference

In Ms Excel, a relative cell reference is a type of reference that adjusts based on its position when copied or filled into other cells. When you use a relative cell reference, Excel automatically adjusts the reference based on the relative position of the formula.

In the first row ranges are A18->A23 and B18->B23.

In the second row, ranges have been adjusted as A19->A24 and B19->B23.

Absolute Reference

In Ms Excel, an absolute cell reference is a type of reference that remains constant when copied or filled into other cells. We need to add a $ symbol.

SUMIFS FUNCTION

The SUMIFS function is a powerful function in Ms Excel that allows you to sum values in a range based on multiple criteria. It is useful when you want to calculate the sum of values that meet specific conditions.

COUNT FUNCTION

It returns the count only for numeric columns.

COUNTA FUNCTION

It returns the count of text columns.

COUNTIF FUNCTION

It counts the number of cells that meets specific criteria.

Using Logical Operators

COUNTIFS FUNCTION

The COUNTIFS function allows you to count the number of cells in a range that meet multiple criteria.

COUNTBLANK FUNCTION

This is used to count all blank cells.

AVERAGE FUNCTION

Used to calculate the average.

AVERAGEIF FUNCTION

It calculates the average of cells that meets specific criteria.

AVERAGEIFS FUNCTION

This function in Ms Excel is used to calculate the average of a range of cells that meet multiple criteria.

MAX FUNCTION

This function finds the highest number in a given range.

MIN FUNCTION

Finds the smallest value from a given range.

MAXIFS FUNCTION

This function in Ms Excel is used to find the maximum value in a range of cells that meet one or more specified criteria.

MINIFS FUNCTION

This function in Ms Excel is used to find the minimum value in a range of cells that meet one or more specified criteria.

ROUND FUNCTION

This function in Microsoft Excel is used to round a number to a specified number of digits or decimal places.

Here the numbers are in 6 decimals.

ROUNDUP AND ROUNDDOWN FUNCTION

This function rounds a number up to a specified number of digits or decimal places, always increasing the value.

This function rounds a number down to a specified number of digits or decimal places, always decreasing the value.

EVEN AND ODD FUNCTION

In Ms excel, the EVEN and ODD functions are used to round numbers to the nearest even or odd integer, respectively.

SUBTOTAL FUNCTION

In Excel, the subtotal function is used to perform calculations on a range of cells.

The function has two arguments: the first argument specifies the type of calculation to be performed, and the second argument specifies the range of cells to include in the calculation.

the values are

If a row is hidden

On entering a value from ignore hidden column the function doesn't count the hidden rows.

SUMPRODUCT FUNCTION

This function returns the sum of products of corresponding ranges.

Double Negative Function

In Excel, the double negative function is often used as a technique to convert a logical value or text representation of a number into an actual numeric value.

SEQUENCE FUNCTION

The sequence function allows to generate a list of sequential numbers in an array.

the trick to counting rows

AGGREGATE FUNCTION

It returns the aggregate calculations using max, count and average.

some values to remember

RANDARRAY FUNCTION

This function generates an array of random numbers between two values.