List of Excel Formulas

SUM Formula

The SUM function ii the aggregates values from a selection of columns or rows from your selected range.

=SUM(number1, [number2], …)

=SUM(B2: G2) – A simple selection that sums the values of a row.

=SUM(A2: A7, A9, A12: A15) – The sophisticated collection that sums values from range A2 to A7, skips A8, adds A9 jumps A10 and A11, then finally adds from A12 to A15.

=SUM(A2: A8)/20 – Shows you can also turn your function into a formula.

AVERAGE Formula

The AVERAGE function should remind you of simple averages of data such as the average number of shareholders in a given shareholding pool.

=AVERAGE(number1, [number2], …)

=AVERAGE(B2:B11) – Shows a simple average, also similar to (SUM(B2:B11)/10)

 

COUNT Formula

 

The COUNT function counts all cells in a given range that contain only numeric values

=COUNT(value1, [value2], …)

=COUNT(A:A) – Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows.

=COUNT(A1:C1) – Now it can count rows.

 

IF Formula

The IF function is often used when you want to sort your data according to a given logic. The best part of the IF formula is that you can embed formulas and function in it.

=IF(logical_test, [value_if_true], [value_if_false])

=IF(C2<D3, ‘TRUE,’ ‘FALSE’) – Checks if the value at C3 is less than the value at D3. If the logic is true, let the cell value be TRUE, else, FALSE

 

MAX & MIN Formula

The MAX and MIN functions help in finding the maximum number and the minimum number in a range of values.

=MIN(number1, [number2], …)

=MAX(number1, [number2], …)

 

AND & OR Formula

These are the two most popular logical functions to check multiple criteria. The difference is how they do this:

 

AND returns TRUE if all conditions are met, FALSE otherwise.

OR returns TRUE if any condition is met, FALSE otherwise.

 

=IF(AND(A2>60, B2>60), "TRUE", "FALSE")

=IF(OR(A2>60, B2>60), "TRUE", "FALSE")

 

TEXT

=TEXT(Value you want to format, "Format code you want to apply")

=TEXT(TODAY(),"MM/DD/YY") Today’s date in MM/DD/YY format, like 03/14/12

=TEXT(TODAY(),"DDDD") Today’s day of the week, like Monday

=TEXT(0.285,"0.0%") Percentage, like 28.5%