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

Skills: Data Entry, Data Processing, Mathematics, Visual Basic

See more: advanced excel help, excel help button, how to use excel, excel help formulas, excel functions, excel help forum, free excel tutorial, microsoft excel tutorial, add sort order featured products, excel data entry columns, oscommerce add comments order, add notes order commerce, excel help label format, comerce add text order, shop script add amount order, excel help shift work, excel vba lock columns, excel vba incrementing columns, visual basic excel help replacing values, excel convert multiple columns

About the Employer:
( 2 reviews ) Kuwait, Kuwait

Project ID: #18958750

Awarded to:


Hi there! This project really interest me! I'm a dedicated professional with experience in this area.I guarantee my work 100%. • Very high speaking and writing skills • Hard, motivated worker • Highly efficient More

$10 USD in 1 day
(0 Reviews)

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

$55 USD in 1 day
(23 Reviews)

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

$50 USD in 2 days
(8 Reviews)

Hi, I am Monir and it will be my pleasure to assist you with this project. I have read your project description You can expect from me I strong commitment to a deadline, the highest quality, and a fluid/friendly c More

$15 USD in 1 day
(3 Reviews)

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.

$20 USD in 1 day
(0 Reviews)

It's piece of a cake. It would be more suitable if you will do it in the google sheets. The programming is more efficient in google scripts. However, it is your decision to make. I would deliver the working project in More

$200 USD in 15 days
(0 Reviews)