Getting started with excel-formula
Excel formulas are used to process simple or complex equations and output the result to a specific cell within a worksheet.
(There is an exception to this where the
WorksheetFunction class is used in VBA programming, however VBA is out of scope for this tag - please refer to the
vba documentation for VBA related articles.)
Formulas can be used to convert, manipulate or evaluate many different kinds of expressions using all different kinds of data. There are however some restrictions around formulas and what they are able to achieve.
- Formulas cannot change any part of a worksheet or contents of another cell.
- Formulas can only return numbers precise up to 15 digits.
- Formulas cannot contain more than 8,192 characters.
- Formulas cannot reference more than 2,048 other ranges.
- Formulas cannot use more than 255 arguments in any given function.
- Dates used in calculations must be in the range of 1900-01-01 to 9999-12-31
Example of an excel formula
|Column||Column A||Example formula||Example formula|
Installation or Setup
Detailed instructions on excel-formula(s).
There are many formulas that you can choose from. They are divided up into 7 different categories and are on the FORMULAS tab in Excel. The categories are:
- Date & Time
- Lookup & Reference
- Math & trig
- More Functions
Then there are also sections for
- Recently used
If you are not sure which one to use you can use the
Insert Function button to help with determining which one to use.
You do not have to use just one formula at a time, you can combine them to get the results that are needed or combine them with other things like
Named Ranges are also on the FORMULAS tab in the Define Name section. You can use the Name Manager to create or modify the ranges and names of the ranges. There are also three other buttons there for Define Name to define and apply names, Use in Formula to choose a defined name and insert it into a formula, and Create from Selection which will create a named range with the name based on the top-left most cell in the range.
There is also the Formula Auditing section. This section will help in troubleshooting a formula. Here you can trace the formula and see what exactly it is doing.
Finally, there is the Calculation section which will allow you to turn on/off the automatic calculation of all formulas and to manually calculate any formulas whether auto-calculate is on or not.
There is no additional installation required for excel-formula on top of what is already required for excel. Please refer to excel documentation.
Excel cell references
You can use cell references without row numbers.
For instance, the formula
=sum(C:C) will add up all the values in column C of the active sheet. This is helpful when you are adding and removing rows but don’t want to update your formulas each time.
There are some instances when using this whole column reference is not a good idea. There is a good article here. It discusses many different variables and test cases to explain when it would be beneficial to use something like
=sum(C:C) or to use something like
|1||Bob||4||the formula: =sum(C:C) =||20|