Basic excel formula and functions

How do we define formula and functions in excel?

Formula: The value of the cell is calculated based on the expression written e.g. =D2-C2, which subtracts the value of cell C2 from the value in cell D2.

Function: Functions are prewritten formula built into the Excel software that apply a specific calculation based on the arguments provided.

The most useful excel functions

SUM(Number1,Number2,..)

The SUM function adds all the data within the range together, for example; =SUM(A1:A4) will return the same result as =A1+A2+A3+A4

MID(text, start number, Number of characters)

The MID function can pick out a certain section of a longer string of text.

MID(cell to evaluate, start position, number of characters to count)

So in the above example we are analysing cell B3, starting at character 7 and returning the next 7 characters of the string.

 

CONCATENATE(text1, text2,…)

The concatenate fuction allows multiple cells to be merged into a single cell.  Additional text can also be added into the new string.

CONCATENATE(Value 1,Value2,Value3,..)

In the above example the merged cells A2, B2 and C2 have been interjected with additional text “sales of” and “sold by” to produce the sentence in the 4th column.

Example of CONCATENATE function
=CONCATENATE(A2,” sales of “,B2,” “,”sold by “,C2)

An alternative to the CONCATENATE function is the use of the “&” symbol.

The above formuala could be replaced by

=A2&”sold by”&B2&” “&”sold by”&C2

 

IF(logical test, [value if true], [value if false])

The IF function allows us to test for a specific condition within a dataset through the use of a  logical argument and then returning different values for true or false results.

How to write an IF function

=IF(logical argument, value if true, value if false)

Logical argument – The argument must be criterion that can be evaluated as True or False

Value if true – This can be a number or a text string if written between parentheses.

Value if false – as above

Example of IF function

The formula used in the above example:-

Example of IF function
=IF(D3<=8,”Qualified”,”Fail”)

This example allocates a Qualified or Fail tag to each candidate based on if they are in the top 8 of the 15 candidates.

 

VLOOKUP(lookup value, table array, column index number, range lookup)

A Vlookup simply locates something in a range of cells and returns an item on the same row from a different column as defined by you.

Structure of a VLookup

Example of VLOOKUP function
VLOOKUP(H2,B3:E10,2,FALSE)

=VLOOKUP(criteria, data range, Column to be returned, exact match?)

 

Criteria – The value you wish to be found in the data table

Data range – the range of data that is to be investigated

Column – the column from which you want data to be returned

Exact match – True (use if an appropriate match is acceptable), FALSE (use if exact match is required)

From the above example we can see that the only difference in the formula is that the column number has changed from 2 to 4.

By changing the value in the “Country” cell we get values returned for the appropriate country.

HLOOKUP(lookup value, table array, row index number, range lookup)

HLookup works on the same premise as VLookup but instead of looking up a value from a column and returning a value from a second column.  HLookup looks horizontally across the role and returns a value from another row as specified.

From the data specified below

The HLOOKUP function make up: –

=HLOOKUP(value criteria, range, Exact match?)

 

SUBTOTAL(function number, ref1,..)

The SUBTOTAL function is a more powerful version of the SUM function due to additional arguments built into the formula.

The SUBTOTAL function is built as below: –

=SUBTOTAL(arguments, range start : range end)

The following arguments can be applied:-

The main advantage of the SUBTOTAL() function over the SUM() function is that if we include other SUBTOTALS() in the list they are ignored in the totaling.

As can be seen above the SUBTOTAL excludes row 12 from the caluclation whereas the SUM function counts row 12.

COUNT(value1, value2,..)

The COUNT() function count the number of cells that contain a numeric value within the specified range.

Lesson 1: Excel the layout

Lesson 3: Formatting cells