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
6 freelancers are bidding on average $58 for this job
Hi there My name is Carlos and I'm an expert in .Net and sql server. I have been working in software developement for the last 20 years. I have many experience in desktop and web development. Regards
i can do this. i have good command over ms excel especially pivot table, charts, advance formulas, formatting, macro, VBA etc. Please review my profile and inbox [login to view URL] Please share your file
Hello, Sir,I read all of your details very carefully & I'm interested to work with you. I hope that you will give me a chance to work with [login to view URL] for you kind response.