Excel Masterclass - Parts 3 & 4

Identify which of the following is the valid syntax for the Excel SUMIF function:

• =SUMIF(range,criteria,[sum_range])

• =SUMIF(range,[value_if_true],[value_if_false])

• =SUMIF([range],[criteria],sum_range)

• =SUMIF(criteria, range,[sum_range])

Name one way that I can use to discover whether Excel has an inbuilt function for what I want to do?

• Type 'Help' in the active cell

• Access Help via the backstage

• Use the search bar at the top of Excel window

• Use the name manager to locate the help file within Excel

As well as adding cell values together AutoSum can also:

• Calculate the maximum value in the range

• Average cell values

• Count Numbers

• All of the above

Which function will give the correct number of quantities (i.e. 4 - excluding 'None')

• COUNT(A2:A6)

• COUNTA(A2:A6)

• COUNTIF(B2:B6)

• COUNT(B2:B6)

When looking at the list of arguments for a function the square brackets around one or more arguments represent:

• The argument must be populated

• The argument is optional

If cell B34 contains the value 536 what would be the result of the following formula:

=IFS(B34>1000,"Excellent",B34>800,"Good",B34>500,"OK",B34>200,"Poor")

• Excellent

• Good

• Poor

• OK

If the formulas in column G were calculated what would be the sequence of answers reading down from cell G3 to G6

• No, No, Yes, Yes

• Yes, No, Yes, No

• No, Yes, No, Yes

• Yes, Yes, No, No

Where can you edit the values field, in a pivot table, to change the calculation from SUM to AVERAGE

• Click on the Data ribbon and change the Value Field Settings

• Click on the field name in the PivotTable Fields window and select Value Field Settings

• Click on Grand Totals on the Design ribbon and change the total from SUM to AVERAGE

• Click on Field Settings in the Home ribbon

What is one of the main advantages of XLOOKUP over VLOOKUP

• XLOOKUP can lookup multiple values in the same cell

• XLOOKUP can return results from any column whereas VLOOKUP can only return values from columns to the right of the lookup values column

• XLOOKUP allows you to format the results

• XLOOKUP is available in older versions of Excel

What are the correct labels for the picture to the left?

• A: Filters

  B: Rows

  C: Columns

  D: Values

• A: Filters

  B: Columns

  C: Rows

  D: Values

• A: Rows

  B: Columns

  C: Filters

  D: Values

• A: Filters

  B: Columns

  C: Rows

  D: Formulas