LARGE The LARGE function is used to return a value dependent upon its ranking within a range of values.
For example, the LARGE function can return the 3rd largest number from a range of values.
The syntax for the LARGE function is; =LARGE(array, k) Array - the range of cells you want to find the k-th largest value within K - the position in the range of values, from the largest, you want to return For example, =LARGE(B2:B10,4) returns the 4th largest number from the range B2:B10.
TRIM The TRIM function removes unwanted spaces from a cell.
It will remove the spaces preceding and following any text within the cell, using accumulated as a result of importing the data from a database.
The syntax for the TRIM function is; =TRIM(text) Text - the reference of the cell you wish to remove spaces from.
For example, =TRIM(C4) will remove any preceding and following spaces from the text in cell C4.
TODAY The TODAY function returns today's date from the computers system clock.
On its own it is not that impressive, but when nested inside another function or with a feature like Conditional Formatting it becomes very useful.
The syntax for the TODAY function is; =TODAY() For example, if you wanted to use Conditional Formatting to highlight dates as they expire within Excel, you would write the <=TODAY() formula.
This then calculates the date each day to see if the dates in the spreadsheet have expired.
IF The IF function tests a condition and takes an alternative course of action depending on the result.
The syntax for the IF function is; =IF(logical_test, [value_if_true], [value_if_false]) Logical test - the condition to be tested Value if true - the action to take if the result is true Value if false - the action to take if the result is false For example, the function below will display the text Pass if cell B4 is 75% or more, and display the text Fail if cell B4 is less than 75%.
=IF(B4>=75%, "Pass", "Fail") VLOOKUP The VLOOKUP function looks vertically down a list, finds a record and then returns information about that record.
VLOOKUP always searches in the first column of a list of records.
The syntax for the VLOOKUP function is; =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Lookup value - the value you want to search for Table array - the list of data you want to search for the value within Col index num - the column number holding the data you want to return Range lookup - enter True for an approximate match, or False for an exact match.
This argument is optional and if omitted Excel will enter True.
For example, if we had a list of employees and the first column contained the employees ID, and column 4 contained the employees date which they joined the company.
We would like to run a VLOOKUP function to search our list of 9000 employees and return a specific employee start date.
For this example lets imagine the list of employees is in range A1:D9001 with row 1 being a header row.
The ID of the employee we are searching for will be entered in cell F2.
The following VLOOKUP will return what we need; =VLOOKUP(F2, A1:D9001, 4, FALSE)
For example, the LARGE function can return the 3rd largest number from a range of values.
The syntax for the LARGE function is; =LARGE(array, k) Array - the range of cells you want to find the k-th largest value within K - the position in the range of values, from the largest, you want to return For example, =LARGE(B2:B10,4) returns the 4th largest number from the range B2:B10.
TRIM The TRIM function removes unwanted spaces from a cell.
It will remove the spaces preceding and following any text within the cell, using accumulated as a result of importing the data from a database.
The syntax for the TRIM function is; =TRIM(text) Text - the reference of the cell you wish to remove spaces from.
For example, =TRIM(C4) will remove any preceding and following spaces from the text in cell C4.
TODAY The TODAY function returns today's date from the computers system clock.
On its own it is not that impressive, but when nested inside another function or with a feature like Conditional Formatting it becomes very useful.
The syntax for the TODAY function is; =TODAY() For example, if you wanted to use Conditional Formatting to highlight dates as they expire within Excel, you would write the <=TODAY() formula.
This then calculates the date each day to see if the dates in the spreadsheet have expired.
IF The IF function tests a condition and takes an alternative course of action depending on the result.
The syntax for the IF function is; =IF(logical_test, [value_if_true], [value_if_false]) Logical test - the condition to be tested Value if true - the action to take if the result is true Value if false - the action to take if the result is false For example, the function below will display the text Pass if cell B4 is 75% or more, and display the text Fail if cell B4 is less than 75%.
=IF(B4>=75%, "Pass", "Fail") VLOOKUP The VLOOKUP function looks vertically down a list, finds a record and then returns information about that record.
VLOOKUP always searches in the first column of a list of records.
The syntax for the VLOOKUP function is; =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Lookup value - the value you want to search for Table array - the list of data you want to search for the value within Col index num - the column number holding the data you want to return Range lookup - enter True for an approximate match, or False for an exact match.
This argument is optional and if omitted Excel will enter True.
For example, if we had a list of employees and the first column contained the employees ID, and column 4 contained the employees date which they joined the company.
We would like to run a VLOOKUP function to search our list of 9000 employees and return a specific employee start date.
For this example lets imagine the list of employees is in range A1:D9001 with row 1 being a header row.
The ID of the employee we are searching for will be entered in cell F2.
The following VLOOKUP will return what we need; =VLOOKUP(F2, A1:D9001, 4, FALSE)