Arguments are the values that functions use to perform calculations. In spreadsheet programs such as Excel and Google Sheets, functions are just built-in formulas that carry out set calculations and most of these functions require data to be entered, either by the user or another source, in order to return a result.
Function Syntax
A function's syntax refers to the layout of the function and includes the function's name, parenthesis, comma separators, and its arguments.
The arguments are always surrounded by parentheses and individual arguments are separated by commas.
A simple example, shown in the image above, is the SUM function, which can be used to sum or total long columns or rows of numbers. The syntax for this function is:
SUM (Number1, Number2, .. Number255)
The arguments for this function are: Number1, Number2, .. Number255
Number of Arguments
The number of arguments that a function requires varies with the function. The SUM function can have up to 255 arguments, but only one is required — the Number1 argument. The remainder are optional.
The OFFSET function, meanwhile, has three required arguments and two optional ones.
Other functions, such as the NOW and TODAY functions, have no arguments, but draw their data — the serial number or date — from the computer's system clock. Even though no arguments are required by these functions, the parentheses, which are part of the function's syntax, must still be included when entering the function.
Types of Data in Arguments
Like the number of arguments, the types of data that can be entered for an argument will vary depending upon the function.
In the case of the SUM function, as shown in the image above, the arguments must contain number data, but this data can be:
Other types of data that can be used for arguments include:
Nesting Functions
It is common for one function to be entered as the argument for another function. This operation is known as nesting functions and it is done to extend the capabilities of the program in carrying out complex calculations.
For example, it is not uncommon for IF functions to be nested one inside the other as shown below.
In this example, the second or nested IF function is used as the Value_if_true argument of the first IF function and is used to test for a second condition, if the data in cell A2 is less than 100.
Since Excel 2007, 64 levels of nesting are permitted in formulas. Prior to that, only seven levels of nesting were supported.
Finding a Function's Arguments
Two ways of finding the argument requirements for individual functions are:
Excel Function Dialog Boxes
The vast majority of functions in Excel have a dialog box, as shown for the SUM function in the image above, that lists the required and optional arguments for the function.
Opening a function's dialog box can be done by:
Tooltips: Typing a Function's Name
Another way to find out a function's arguments in Excel and in Google Sheets is to:
In Excel, the tooltip window surrounds optional arguments with square brackets ([ ]). All others listed arguments are required.
In Google Sheets, the tooltip window does not differentiate between required and optional arguments. Instead, it includes an example as well as a summary of the function's use and a description of each argument.
Posted by3 years ago
Archived
I failed a test today because i couldn't get the COUNTIFS formula to work properly. I came straight home and tried to replicate the test on my own. There's 2 sheets, 1 with data and 1 for analysis
You can download the file here: https://www.dropbox.com/s/z5o93atty8d4t9l/Practice.xlsx?dl=0
Sheet 1 'Medals' http://imgur.com/SGOVrO8
Grey is Given, Blue is Calculated
I used generic country names and a random number generator for number of medals. Columns C through E are summed in Column B. Colums G through I are summed in Column F.
The formula in column J is =IF(C2>0,ROUNDUP((B2/C2),),0)
The formula in column K is =IF(G2>0,ROUNDUP((F2/G2),),0)
I forgot the exact wording of what it wanted me to find, but i could see a screenshot of the correct final answers as i took the exam and i know i used the formula correctly, both here and on the test.
Sheet 2 'Analysis' http://imgur.com/E2Tn7OF
In the 4th column of the table i was to calculate the number of items that matched the Tier 1, 2, and 3 criterias respectively.
Given that there are 2 criteria to meet, 1 for the calculated summer column and 1 for the calculated winter column, i used a COUNTIFS formula. It wanted to know the number of countries that were greater than or equal to BOTH of the given tier thresholds. I know there are other ways to find the answer, but it expressly told me to use a COUNTIFS formula, it even told me each item to put in the formula, and i just can't get it to give me the real answer! It always just returns '1', which by looking at the screenshot its obvious that the answer is more than 1.
Here's the formula i used in cell E3: =COUNTIFS(Medals!J:J,'>=C3',Medals!K:K,'>=D3')
Alternatively =COUNTIFS(Medals!$J$2:$J$226,'>=$C$3',Medals!$K$2:$K$226,'>=$D$3') returns '0'
What am i doing wrong!? :(
12 comments
I an looking to find a value from countifs ,using an array of dates and an array of names.
'Shout out to Jeeped for providing the code for date array below.'
The result is always zero, and 'I think?' its because both of the arrays are of different types? or maybe format is a better word.
I can use the dts array in similar countifs ..see below.
however as soon as I need to introduce another array for say 'names', the countifs line will execute and the code runs, but. just returns zero.
How can I create an array of 'names' that would work with the dts array in a countifs situation?
Thanks!
Edit 4/2/2018 1:00pmAdding Example to show that changing the order still returns 0.
within worksheet use this, or re-order for the other line commented out in the code below.enter image description hereThen using either the active line of the one commented out.
****The answer should be 2
*Date To be within 2018 Or Blank from dts Array*a Column to contain 'Z'*a Column to contain a name from name Array
Ben.Name
Ben.NameBen.Name
1 Answer
Documentation for the
WorksheetFunction commands isn't great. It's a good idea to refer to the documentation for the related worksheet function. (ie., COUNTIFS vs Application.WorksheetFunction.Countifs )
Looks like your added criteria are out of order.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |