A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need Formula Help



 
 
Thread Tools Display Modes
  #1  
Old December 27th, 2007, 08:13 PM posted to microsoft.public.excel.worksheet.functions
Randy Weitzel
external usenet poster
 
Posts: 1
Default Need Formula Help

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


  #2  
Old December 27th, 2007, 08:26 PM posted to microsoft.public.excel.worksheet.functions
ryguy7272
external usenet poster
 
Posts: 1,593
Default Need Formula Help

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


Regards,
Ryan---


--
RyGuy


"Randy Weitzel" wrote:

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


  #3  
Old December 27th, 2007, 08:41 PM posted to microsoft.public.excel.worksheet.functions
Randy Weitzel[_2_]
external usenet poster
 
Posts: 3
Default Need Formula Help

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

"ryguy7272" wrote:

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


Regards,
Ryan---


--
RyGuy


"Randy Weitzel" wrote:

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


  #4  
Old December 27th, 2007, 08:56 PM posted to microsoft.public.excel.worksheet.functions
Trying
external usenet poster
 
Posts: 49
Default Need Formula Help

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" wrote:

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


  #5  
Old December 27th, 2007, 09:30 PM posted to microsoft.public.excel.worksheet.functions
Randy Weitzel[_2_]
external usenet poster
 
Posts: 3
Default Need Formula Help

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

"TRYING" wrote:

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" wrote:

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


  #6  
Old December 27th, 2007, 09:58 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Need Formula Help

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

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
  #7  
Old December 27th, 2007, 10:33 PM posted to microsoft.public.excel.worksheet.functions
Randy Weitzel[_2_]
external usenet poster
 
Posts: 3
Default Need Formula Help

Thanks Ron
Going to give it a shot

Thanks again
Randy

"Ron Rosenfeld" wrote:

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

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.