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
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.
So in the above example we are analysing cell B3, starting at character 7 and returning the next 7 characters of the string.
The concatenate fuction allows multiple cells to be merged into a single cell. Additional text can also be added into the new string.
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.
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
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:-
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
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: –
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: –
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.
The COUNT() function count the number of cells that contain a numeric value within the specified range.