Need Formula Help

[Randy Weitzel]

Hi

I am working on a spreadsheet for dispatching round trips for delivery. I
know how long each trip should take based upon day of the week and
destination and I would like to have those values returned in a cell based
upon the day of the week and destination in two other cells.

I want to return a estimated number of minutes for a round trip based upon
the day of the week and the destination to a cell. I have approximately 75
destinations and of course, 7 days of the week.

Ex: If the day of the week (represented as the number 5) in A1 is Thursday
and the destination in B1 is Tool World, then the number of minutes I want in
C1 to show is 40 minutes.

If the day of the week (represented as the number 6) in A2 is Friday and the
destination in B2 is Tire Land, then the number of minutes I want in C2 to
show is 30 minutes.

And so on. I then plan on adding the number of estimated minutes to the
inserted time in another cell with a formula to give an estimated time of
return.

I am trying to figure out a formula that will accomplish this, but
unfortunately, am in over my head.

Much thanks and appreciation to any that can help

Randy

 

[ryguy7272]

It sounds like a VLookup function will help you get a handle on things:
http://www.contextures.com/xlFunctions02.html


Regards,
Ryan---

 

[Randy Weitzel]

Thanks
Looks like that would work
It will take me a while to apply it to my situation

Thanks again for the quick response

Randy

 

[TRYING]

I don't necessarily have a solution for you. I am trying to learn myself.

So I don't know if this would help you, but it would help me in working out
the solution. In your first example, would the trip to Tool World be other
than 40 minutes in another day, say Saturday? In other words, are there
possibly different time values assigned to each combination of day and
location?

For example, Tool World: Sun 30 min, Mon 45, Tue 40, Wed 35, etc?

 

[Randy Weitzel]

Exactly...the traffic is thicker on different days of the week, so a trip to
Tool World would be 40 minutes on Friday, but would only be 30 minutes on
Saturday, 25 minutes on Sunday, etc.

Thanks for the input and any help

Randy

 

[Ron Rosenfeld]

On Thu, 27 Dec 2007 11:13:08 -0800, Randy Weitzel <Randy

Hi

I am working on a spreadsheet for dispatching round trips for delivery. I
know how long each trip should take based upon day of the week and
destination and I would like to have those values returned in a cell based
upon the day of the week and destination in two other cells.

I want to return a estimated number of minutes for a round trip based upon
the day of the week and the destination to a cell. I have approximately 75
destinations and of course, 7 days of the week.

Ex: If the day of the week (represented as the number 5) in A1 is Thursday
and the destination in B1 is Tool World, then the number of minutes I want in
C1 to show is 40 minutes.

If the day of the week (represented as the number 6) in A2 is Friday and the
destination in B2 is Tire Land, then the number of minutes I want in C2 to
show is 30 minutes.

And so on. I then plan on adding the number of estimated minutes to the
inserted time in another cell with a formula to give an estimated time of
return.

I am trying to figure out a formula that will accomplish this, but
unfortunately, am in over my head.

Much thanks and appreciation to any that can help

Randy

Set up a data table:
1 2 3 4 5 6 7
Tool World 29 23 17 12 21 30 39
Tire World 22 25 18 10 24 38 52
Tire Land 13 21 25 14 16 18 20

Note days of the week in the first row; and destinations in first column.

Name the table: Tbl.

Use this formula:

=VLOOKUP(B1,Tbl,A1+1,0)


--ron

 

[Randy Weitzel]

Thanks Ron
Going to give it a shot

Thanks again
Randy