Level 2 - Lesson 13 - Arguments
Category: [General,Excel] Demo Available
Arguments
Most of Excels formulas take what are known as Arguments. An Argument is defined as:
The values that a Function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numeric values, text values, cell references, ranges of cells, names, labels, and nested functions.
Avoid Typing
When we write a Function or Formula we would normally refer to a cell or a range of cells for it's argument. In these cells would be the numbers or text that we want to use in our calculations.
So we could write the formula: =Sum(A1:A10) which would tell Excel to add together all numeric entries in the range A1:A10. (Text entries are ignored). A good habit you should form (if you haven't already) is to never type your cell references, use the mouse pointer instead. So in the above example you would type: =Sum( then holding down your left mouse button select cells A1:A10. This will eliminate any possible typos. While there is not much chance of a typo with such a simple reference, there is if the formula was:
=SUM(ExternalFormulasReport!IQ1:IR10) or worse still:
=SUM([Book3]ExternalFormulasReport!$IQ$1:$IR$10) and to get really messy you might have:
=SUM('C:\My Documents\Doodlings\[Book3.xls]ExternalFormulasReport'!$IQ$1:$IR$10)
In all three examples we only typed =Sum( . To get a file name in your formula, simply open the file and then in the workbook you want the formula in type the equals sign followed the function name, then open parenthesis, then go to Window options on the View tab, select Switch Window to go to the file you want the formula in and simply select the sheet and cell(s) you want. (Or you can just select the file from your task bar located at the bottom of your screen). The complete file name and path can be viewed in your formula bar (or in the cell on your worksheets) and will be automatically placed in the formula when you hit Enter to complete it. You could of course use this method when referencing a particular range on a particular Worksheet within the same Workbook.
While this is a relatively simple method it can at times be a bit awkward if the formula is long and/or you need to keep going backwards and forwards from one Workbook/Worksheet to another. The method I use when presented with this is to simply write the formula in the Workbook (or on the Worksheet) I will be referencing, then Cut and Paste it into the Workbook/Worksheet I want the result in. This way Excel does all the Workbook and/or Worksheet referencing for me.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples