Question

263 use goal seek 264 265 create a user defined function 266 use a pivot table 267 40 -INDEX(Data, MATCH ROWINDIRECT1:"&ROWS(Data))-1,coUNTIF(Data, "s"&Data),O)) 268 Write out, in several steps, what the above formula does, from beginnig to end: 269 270 271 Short Answer 272 1 IFin-0,0,SUMIFMODIROWONDIRECTC1 "&COUNT(Data))1.n)-0,Data, Explain what the above does 273 274 275 2 What is the name of the function that references data from a pivot table 276 277 278 279 280 281 282 283 284 285 286 287 288 289 Sheet Sheet2 + Enter 囲回凹 Page 1 of 1 146 words

Answer 1

Short Answers

1. This statement is an IF statement in a broad sense.

IF takes 4 arguments - if first value is true then second value is passed and if third value is true fourth value is passed.

The SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.

Use the MOD function to get a reminder after division. For Example, MOD(3,2) returns 1, because 2 goes into 3 once, with a remainder of 1.

Use the ROW function to get the row number for a cell reference or a range. For example:

=ROW(A3) // returns 3
=ROW(D5) // returns 5
=ROW(F10:J15) // returns 10

Note the ROW function returns a number for the row in a worksheet.

Use INDIRECT to create or supply a reference in text form. Indirect is useful when you want to convert a text value into a valid cell reference.

The reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. For example, the formula =INDIRECT("A1:A100") will always refer to the first 100 rows of column A, even if rows in that range are deleted or inserted.

• References created by INDIRECT are evaluated in real time and the content of the reference is displayed.
• When ref_text is an external reference to another workbook, the workbook must be open.
• a1 is optional. When omitted, a1 is TRUE = A1 style reference.
• When a1 is set to FALSE, INDIRECT will created a an R1C1-style reference

The COUNT function counts the number of numbers in supplied values. Values can be items, cell references, or ranges. For example, =COUNT(1, 2, "apple") returns 2. And COUNT(A1:A10) will count the number of numeric values in the range A1:A10.

• Count can handle up to 255 additional values.
• Use COUNTA to include text and logical values. Use COUNTIF to count based on criteria.
• Error values or text values that cannot be coerced into numbers are not counted
• The logical values TRUE and FALSE are not counted.
• Empty cells and text are not counted.

2. Use the GETPIVOTDATA function to query an existing pivot table and retrieve specific data based on the pivot table structure. The first argument (data_field) names a value field to query. The second argument (pivot table) is a reference to any cell in an existing pivot table.