MGT 2210 - Excel

If you have the algebraic expression 3x^2 - 2x^2 + x -1, how would you write this in Excel if x is the value in cell F5?

=3F5^3-2F5^2+F5-1

Given the formula for the volume of a sphere is (4/3)PIR^3, how would you calculate the radius of the sphere in Excel?

=(3/4)*(A2/PI())^(1/3) where A2 is the volume of the sphere.

Given a football field with length 109.80 meters and width 48.80 meters, how would you calculate the distance traveled after running around the field six times in Excel?

=6(2A2+2*B2) where A2 is the length and B2 is the width.

If you have the algebraic expression 2x+3, how would you write this in Excel if x is the value in cell A1?

=2*A1+3

If a ream of paper contains 500 sheets and is 4.895 cm thick, how would you determine the thickness of a single sheet in Excel?

=B2/A2 where B2 is the total thickness and A2 is the total number of sheets.

In Excel, what is the purpose of using parentheses in formulas?

Parentheses are used to change the order of operations in a formula.

If you have a formula in Excel as =2(3+4)5, what will the formula return?

70

In the formula =(2+3)*4, why does Excel calculate the addition before the multiplication?

Because parentheses override the standard order of operations, telling Excel to calculate the addition before the multiplication.

If you have a formula in cell B2 as =A1+1 and you copy this formula to cell B3, what will the formula in B3 be if the reference to A1 is relative?

=A2+1

If you have a formula in cell B2 as =$A1+1 and you copy this formula to cell C2, what will the formula in C2 be?

=$A1+1

Which symbol is used in Excel to make cell reference absolute?

$

How does an absolute cell reference behave when a formula is copied to another cell?

The reference remains constant.

If you have a formula in cell B2 as =$A$1+1 and you copy this formula to cell B3, what will the formula in B3 be if the reference to A1 is absolute?

=$A$1+1

If you have the algebraic expression sqrt(x), how would you write this in Excel if x is the value in cell C3?

=SQRT(C3)

If you want to convert the distance traveled from meters to miles (given 1 mile = 1609.3 meters) in Excel, which formula would you use?

=D2/1609.3 where D2 is the distance in meters.

What type of cell reference is =$A1 in Excel?

If you have a formula in cell B2 as =A$1+1 and you copy this formula to cell B3, what will the formula in B3 be?

=A$1+1

How would you represent the algebraic expression x/y in Excel, where x is the value in cell D4 and y is the value in E4?

=D4/E4

How does a relative cell reference behave when a formula is copied to another cell?

The references changes based on the relative position of rows and columns.

What happens if Goal Seek can't find a solution?

Excel will return the closest possible solution.

How many lists of input values does a two-variable data table use in its formula?

Two

What are the three parameters you need to set for the Goal Seek function?

Set cell, To value, and By changing cell

What is the correct syntax for the FILTER function in Excel?

=FILTER(array, include, [if_empty])

Can the FILTER function in Excel return multiple rows and columns?

Yes, it can return an array that includes multiple rows and columns.

If you have a formula in Excel as =FILTER(A1:B10, B1:B10="Yes", "No data"), what will the formula return if there are no cells with "Yes" in the range B1:B10.

"No data"

Can the XLOOKUP function in Excel return multiple rows and columns?

Yes, it can return an array that includes multiple rows and columns.

What is the correct syntax for the XLOOKUP function in Excel?

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

If you have a formula in Excel as =XLOOKUP("Apple", A1:A10, B1:B10, "Not found"), what will the formula return if there is no "Apple" in the range A1:A10?

"Not found"

What does the combination of INDEX and MATCH functions in Excel do?

It can be used to perform a two-way lookup, returning a value at the intersection of a particular row and column.

What is the correct syntax for combining the INDEX and MATCH functions in Excel?

=INDEX(array, MATCH(lookup_value, lookup_array, match_type))

If you have the formula in Excel as =INDEX(A1:B10, MATCH("Orange", A1:A10, 0), 1), what will the formula return?

The value in column A of the row where "Orange" is found in the range A1:A10.

How do you sum two columns in a PivotTable in Excel?

Create a calculated field that adds the two columns.

How can you combine rows or columns in a PivotTable in Excel?

By using the Group feature on the Analyze tab.

What happens when you drag more than one field to the columns area in a PivotTable in Excel?

It creates a multi-level pivot table, with each additional field providing a further level of detail.

What happens when you drag more than one field to the Rows area in a PivotTable in Excel?

It creates a multi-level pivot table, with each additional field providing a further level of detail.

What is the effect of rearranging the fields in the Rows or Columns area of a PivotTable in Excel?

It changes the order of detail levels in the multi-level pivot table.

What is the first step in creating a pivot table in Excel?

Click the Insert tab and then click the PivotTable icon.

What is a two-variable data table in Excel?

It is a feature to use when you want to see how your result would change when two of the input values change at the same time.

Where can you find the Goal Seek function in Excel?

Under the Data tab in the What-If Analysis tools.

What does the FILTER function in Excel do?

It returns an array that meets certain conditions.

What does the XLOOKUP function in Excel do?

It can find things in a table or range by row.

Can the combination of INDEX and MATCH functions in Excel return multiple rows and columns?

No, it can only return a single cell.

What happens when you combine rows in a PivotTable in Excel?

It groups the rows together and creates a new, higher level of data summary.

What happens when you combine columns in a PivotTable in Excel?

It groups the columns together and creates a new, higher level of data summary.

How do you add multiple fields to a PivotTable in Excel?

Click and drag a field to the Rows or Columns area.

If you have a formula in Excel as =FILTER(A1:B10, A1:A10>5, "No data"), what will the formula return if there are no values greater than 5 in the range A1:A10?

"No data"

How do you create a calculated field in a PivotTable in Excel?

On the Analyze tab, in the Calculations group, click Field, Items and Sets, and then click Calculated Field.

How can you rearrange the fields in the Rows or Columns area of a PivotTable in Excel?

By clicking and dragging the fields to the desired position

What is the correct syntax for the IF function in Excel?

=IF(logical_test, value_if_true, value_if_false)

How does the IFS function in Excel differ from the IF function?

The IFS function can handle multiple conditions without the need for nesting.

If you have a nested IF formula in Excel as =IF(A1>10, "High", IF(A1>5, "Medium", "Low")), what will the formula return if the value in cell A1 is 12?

If you have a formula in Excel as =IF(A1>10, "Yes", "No"(, what will the formula return if the value in cell A1 is 15?

Yes

If you have a formula in Excel as =IFS(A1>10, "High", A1>5, "Medium", TRUE, "Low"), what will the formula return if value in cell A1 is 7?

Medium

If you have a formula in Excel as =OR(A1>10, B1<20), what will the formula return if the value in cell A1 is 5 and the value in cell B1 is 15?

TRUE

What does the XOR function in Excel do?

It returns TRUE if exactly one of the agreements is TRUE, and returns FALSE otherwise.

What does the NOT function in Excel do?

It returns the opposite of the boolean value of the argument.

If you have a formula in Excel as =OR(A1>10, B1<20, C1=5), what will the formula return if the value in cell A1 is 5, the value in cell B1 is 15, and the value in cell C1 is 3?

TRUE

If you have a formula in Excel as =NOT(A1>10), what will the formula return if the value in cell A1 is 15?

FALSE

If you have a table named "Tasks" with columns "TaskID" and "TaskHours", how would you reference the entire table in a formula?

=Tasks[]

If you have a table named "Sales" with columns "Product" and "Price", how would you reference the "Price" column in a formula?

=Sales[Price]

What is the benefit of using structured references in Excel?

They make formulas easier to read

How do you include structured references in your formula?

Click the table cells you want to reference instead of typing their cell reference in the formula.

Which Excel function computes the arithmetic mean of its arguments?

AVERAGE

Which Excel function identifies the smallest value among cells that satisfy a given condition or criteria?

MINIF

Which Excel function computes the total sum of all cells that meet multiple criteria?

SUMIFS

Which Excel function computes the arithmetic mean of all cells that meet multiple criteria?

AVERAGEIFS

Which Excel function identifies the smallest value among all cells that meet multiple criteria?

MINIFS

Which Excel function computes the arithmetic mean of cells that satisfy a given condition or criteria?

AVERAGEIF

If you have a column of numbers in the range A1 through A10, what formula would you use to calculate the average of these numbers?

=AVERAGE(A1:A10)

What does the IFERROR function in Excel do?

It returns a custom result when a formula generates an error, and the original result when no error is detected.

What is the correct syntax for the IFS function in Excel?

=IFS(test1, value1, [test 2, value 2],...)

Which of these is not a boolean operator in Excel?

AND, XOR, IF, OR

IF

What is a structured reference in Excel?

A special syntax for referencing Excel Tables.

The @ symbol in the structured reference is...

The notation to refer to values on the current row of the table.

What is the syntax for the AVERAGEIFS function in Excel?

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

What is the syntax for the SUMIF function in Excel?

=SUMIF(range, criteria, [sum_range])

What is the syntax for the AVERAGEIF function in Excel?

=AVERAGEIF(range, criteria, [average_range])

What is the sntax for SUMIFS function in Excel?

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

What is the syntax for the INDEX funciton in Excel?

=INDEX(array, row_num, [column_num], [area_num])