Formulas can be made much more efficient by using worksheet functions.
The built-in worksheet functions can be used to perform calculations, data manipulation and help to analyse data.
A function can either be the only item in a formula or can be part of a larger formula.
Functions - Complete List
This Complete List of Functions provides details and examples for all the worksheet functions.
Formulas - Complete List
This Complete List of Formulas provides details and examples.
Using Worksheet Functions
Worksheet functions can reduce the risk of errors and improve the overall efficiency of your workbook.
Functions can be thought of as being made up of three parts: an equal sign, a function name and a list of arguments enclosed in brackets.
Worksheet functions can only be used within a formula and the function name is always followed by a set of parentheses.
The SUM function is a great example. This can be used to quickly add up the values in a range of cells.
The cells in column H are used to illustrate the functions used in the adjacent cells.
It is very common for your formulas to only contain worksheet functions. The cells G2, G3 and G4 are examples.
Functions are identical to formulas in the sense that they are updated automatically when the arguments change.
Any changes to the contents of a cell are automatically reflected in any formulas that refer to that cell.
You must have parentheses around all of your arguments and the opening parentheses must appear immediately after the name of the function.
Some of the functions that are available are equivalent to complicated formulas which you would otherwise have to create by hand.
Although formulas are very useful and can be quite complicated some of the functions such as the "Lookup and Reference" cannot be reproduced using formulas.
If you receive the error "Undefined Function" or "Function Not Available" you may have lost one of your library references or ones of your addins.
You can always write your own worksheet functions using VBA when you need a function with more flexibility.
Remember to include a speech mark character in a function you must use double speech marks.
All formulas or formulas containing functions must start with an equal sign (=).
Valid function names are converted to uppercase automatically after you press Enter, so always enter function names in lowercase. This verifies that you have typed them correctly.
Some worksheet functions will format the cells to an appropriate format after they have been entered. This only happens if the format is the General number format (e.g. NOW).
Remember to check your (Excel Options)(Advanced, Display options for this worksheet - show a zero in cells that have zero value). This controls whether empty cells contain a zero or not.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopNext