OzGrid

Lesson 18 - Excel Formulae Arguments & Syntax

< Back to Search results

 Category: [Excel]  Demo Available 

EXCEL FORMULAE ARGUMENTS & SYNTAX

 

Lesson 18 - Excel Arguments and Syntax in Formulas

EXCEL FORMULAE ARGUMENTS & SYNTAX

Now we have been through the ways and means of referencing cells we can move on to the SUM Function in Excel. Without doubt the SUM function is one of the most commonly used Excel functions. I will also use the SUM function to better explain what Arguments are in functions and what the Syntax of a Function is.

Arguments

Most of Excels functions need values of some sort to perform calculations and it is these values that are known as arguments. The argument for a function could be in the form of a number, text or logical value such as TRUE or FALSE. They could also be error values or arrays, but we won't go into this at this level.

Some functions will take only one argument while others can take up to 30. The SUM function for example takes up to thirty arguments, but only requires one. There are other functions that take 5 or 6 arguments and require that all the arguments have a value in them. Commas are always used to separate arguments in functions.

Syntax

The term syntax in Excel refers to the order in which arguments are accepted in functions. For example, if we have a function that takes two arguments and the first argument mustbe a number, while the second must be text we could not put the text value as the first argument and the number as the second. If we did we would have the wrong syntax

Now we have discussed Arguments and Syntax we can move on to creating a formula in Excel using the SUM function. The SUM function will add all the numbers in a range of cells. If there is text within the range the SUM function will ignore them. However the SUM function will not ignore text values not stored in cells (a text value is a number entered as text). I will use an example to show you what I mean by this statement.

  • Type The Numbers 1 To 5 In Cells A1:A5.
  • Now In Cell A11 Type =Sum(
  • Click In Cell A1 And Holding Down The Mouse Button Drag Down To Cell A10.
  • Push Enter (Excel Will Automatically Add Our Closing Parenthesis For Us).

You should get the result 15 and if you select cell A11 and look in the formula bar you should see the formula =SUM(A1:A10). If we now type the number 5 in cell A6 we will get the result 20. If we type the word dog in cell A7 our result will not change. If we then type: "5" (including the quotation marks) the SUM function will again not change. This is because the SUM function will ignore text entered in cells.

To see what I mean by my previous statement "However the SUM function will not ignore text values not stored in cells" we will need to add another argument. Remember Commas are always used to separate arguments in functions, so we shall do is what is known as edit our formula. The easiest method to edit a simple function like the SUM is to do so from within the Formula bar.

  • Select Cell A11 Then Place Your Mouse Insertion Point Between The A10 And The Closing Parenthesis.
  • Type A Comma (This Tells Excel We Are Adding Another Argument) Then Type "5" (Including The Quotation Marks)
  • Push Enter.

This time our result in cell A11 will change to 25. This shows that while the SUM function will ignore text values stored in cells it does not ignore text values enter directly as an argument.

The range of cells that we use as an argument in the SUM function do not have to be adjoining as in the above example (A1:A10) they can be in non-adjoining ranges. This is the most likely reason we would use more than one argument.

  • Delete The SUM Formula In Cell A11.
  • Type The Numbers 1 To 5 In Cells D1:D5.
  • Type =Sum( In Cell A11 Then Select The Range A1:A10.
  • Type A Comma (,) To Start Another Argument.
  • Select The Range D1:D5 And Push Enter.

You should get the sum value of all numbers in the ranges A1:A10 and D1:D5

We can make this task slightly easier by selecting the range A1:A10 then holding down the Ctrl key, select range D1:D5 and up to another 28 ranges if we wanted to.

If the numbers we wish to sum reside on another Worksheet, we would simply click the sheet name tab first then select the range on the Worksheet. If we want to sum numbers on both the Worksheets housing the sum formula and on another Worksheet, we could use the first argument for the current sheet and the second argument for the other sheet. If we wanted to sum the same range of cells on different Worksheets that are next to each other we could do so like this:

  • Make Sure You Have Five Worksheets In The Workbook You Are Using. To Do This Go To Insert>Worksheet As Many Times As Needed.
  • In Cell A1 Type =SUM(
  • Now Select The First Sheet, This Is The Sheet On The Far Left. Hold Down Your Shift Key And Select The Name Tab Of The Last Worksheet, This Is The Sheet On The Far Right.
  • Now Release The Shift Key And Select The Range You Wish The SUM Function To Sum.
  • Push Enter.

Depending on the Worksheet names, order and range chosen, your formula should look something like: =SUM(Sheet1:Sheet5!D1:G20)If you now type any numbers within your chosen range on any of the Worksheets, they will affect the result of the SUM formula accordingly. We should also note that if we add another Worksheet anywhere between our chosen sheets it too will have the chosen range included (D1:G20 in my example).

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

 

Go back to:

Lesson 1 - Excel Fundamentals
Lesson 2 - Starting Excel and Excel Workbooks
Lesson 3 - Excel Toolbars and Task Panes
Lesson 4 - Excel Worksheets
Lesson 5 - Excel Cells and Navigating a Worksheet
Lesson 6 - Excel Cut/Copying and Pasting Data
Lesson 7 - Excel Copying with the Fill Handle
Lesson 8 - Excel Paste Special
Lesson 9 - Excel Insert Command
Lesson 10 - Excel's default options
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References
Lesson 17 - Excel: Avoid Typing

See also:

Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions
Lesson 23 - Excel's Named Ranges
Lesson 24 - Excel's Constants and the Paste Name Dialog
Lesson 25 - Excel's Calculations
Lesson 26 - Excel Comments Cell
Lesson 27 - Excel Find and Replace
Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2
Lesson 31 - Sorting in Excel
Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet
Lesson 36 - Excel Worksheet Protection
Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

 

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

 

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.

 


Gallery



stars (0 Reviews)