excel help

You need to take the bus into campus to attend MIS 204. You know that the M bus is the route you need, and you’ve

got the route schedule in Excel. Your task is to write a formula that determines when the next bus will arrive at your

stop, and how long until that time.

1. Enter a time in cell C11.

2. Setup data validation in cell C12 and then choose a stop.

3. Write a formula for cell C14 that uses INDEX & MATCH to determine when the next bus will arrive.

a. You will need to match on two dimensions: stop number and time.

b. Your INDEX array should not include the stop numbers.

c. The MATCH() you setup to determine column number is complex.

i. You will need to use the OFFSET() function in your second MATCH function. The OFFSET

function shifts a range from a starting point x number of rows and y number of columns.

1. Your reference will be cell D1.

2. The number of rows you need to shift downwards is equal to the stop number minus 1.

3. The number of columns you need to shift rightwards is 0.

4. The height is 1

5. Use the COLUMNS() function to determine the width.

ii. Specify the match type (HINT: the times are in ascending order).

d. You will need to add 1 to the results of the second MATCH().

4. Once you have your INDEX & MATCH working, you need to make one last change. If you enter a time that is

when the bus isn’t running (e.g., 1:00AM) the formula will return a #REF error for stops 3 – 9 because you are

asking the INDEX function to return from an array that is not on the range you specified.

a. To fix this problem, put your formula inside an IFERROR() formula.

b. For the value_if_error argument, write another INDEX & MATCH with the INDEX array the first column

of times, and matching the stop number to the column of stop nu

Tiedonsyöttö Tietojenkäsittely Matematiikka Visual Basic

Projektin tunnus: #18958750

Tietoa projektista