INDEX Practice emullens

xlsx

School

University of Wisconsin, Madison *

*We aren’t endorsed by this school

Course

310

Subject

Mechanical Engineering

Date

Jan 9, 2024

Type

xlsx

Pages

5

Uploaded by JusticeGorilla29813

Report
INDEX INDEX The INDEX function returns the value of a particular cell of a rectangular range. You find the cell by specifying its indexes. Specifically, you specify a row index and a column index. For example, the range M11:P15 has 5 rows and 4 columns. The indexes 3, 2 indicate the 3rd row (from the top) and the 2nd column (from the left) of the range. The highlighted cell to the right, which currently contains the value 1000, is the cell at the location with indexes 3, 2. To use the INDEX function: Enter the formula =INDEX(array,row index,column index) , where array is the range of cells to search, row index is an integer (for row) and column index is an integer (for column). For example, the formula =INDEX(M11:P15,3,2) refers to the value in cell N13 (highlighted), the cell in the 3rd row and 2nd column of the range. If you enter this formula in a cell, it will return the value 1000. Exercise 1 Enter =INDEX(M11:P15,3,2) in M21 to see that it returns 1000 from N13. INDEX with a Single Column If the range is a single-column, the column index argument can be omitted. In that case, =INDEX(range,row index) returns the value in the specified row index of the range. The formula =INDEX(M26:M30,2) returns the value in cell M27, the 2nd cell in the range. Of course, it also works if you specify both the row and the column, i.e. =INDEX(M26:M30,2,1). Exercise 2 Enter an INDEX formula in M32 that retrieves 2000 from M27 INDEX with a Single Row If the range is a single-row, only the column index needs to be included. The formula =INDEX(M36:Q36,3) refers to the value in cell O36, the 3rd cell in the single-row range. Of course, it also works if you specify both the row and the column, i.e. =INDEX(M36:Q36,1,3). Exercise 3 Enter an INDEX formula in M38 that retrieves 3000 from O36 Dynamic Formulas INDEX often incorporates changeable inputs for the indexes to see how the result varies with inputs. Then, you reference the cells with the changeable inputs for the row and column indexes. Then the indexes can easily (and visibly) be changed, and INDEX will return the value from the appropriate cell. Exercise 4 In M58, find the unit shipping cost from the Plant indicated in M55 and the City indicated in M56. The formula in M58 should reference the cells M55 and M56, instead of "hard coding" the current values, 1 and 2, in the formula. You want a formula that returns the correct result for any valid inputs, which is an important spreadsheet goal in general. In other words, the formula could find the shipping cost in any cell in the range, depending on the Plant and City numbers. (Scroll to the right for the answer) Save this file and submit it via Canvas.
Save this file and submit it via Canvas.
Column 1 Column 2 Column 3 Column 4 Row 1 Row 2 Row 3 1000 Row 4 Row 5 Ex 1: 1000 Column 1 Row 1 Row 2 2000 Row 3 Row 4 Row 5 Ex 2: 2000 Column 1 Column 2 Column 3 Column 4 Column 5 Row 1 3000 Ex 3: 3000
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Unit shipping costs City1 City2 City3 Plant1 1.25 1.35 1.55 Plant2 1.15 1.45 1.25 Plant3 1.35 1.45 1.15 Plant 1 if this changes, the formula in M58 should return the correct value City 2 if this changes, the formula in M58 should return the correct value Ex 4: $1.35 Unit shipping cost
Unit shipping cost 1.35