Vlookup dilemna

[Beckwa]

Ok, for my spreadsheet for a taxi company I have a cell for "base charge" and one for "price for mile". I want these prices to be dependent on a Vlookup which refers to a lookup prices table. The prices change according to the current time. I tried a Vlookup but that doesn't include the fact that it should be referring to the time before looking up the appropriate value. Any ideas? Am I even speaking English anymore?!?!? B

 

[Yogi Anand]

On 2002-04-27 12:23, Beckwa wrote:
Ok, for my spreadsheet for a taxi company I have a cell for "base charge" and one for "price for mile". I want these prices to be dependent on a Vlookup which refers to a lookup prices table. The prices change according to the current time. I tried a Vlookup but that doesn't include the fact that it should be referring to the time before looking up the appropriate value. Any ideas? Am I even speaking English anymore?!?!? B

Hi Beckwa:
You have spoken EXCELent English but you have taken us for a ride by withholding some essential details. Please post a sample of your data and what formula you are using ... and then let us take it from there!

 

[Beckwa]

What more do you need to know then? I basically want it to say"If BookingTime is greater than 10:00 but less than 16:00 then (VLOOKUP PART) lookup the table and pull off the relevant price, if it's greater than 16:01 but less than 22:00 then lookup table and pull off relevant price etc etc. Now i know i'm not speaking english!!! B

 

[Aladin Akyurek]

On 2002-04-27 12:58, Beckwa wrote:
What more do you need to know then? I basically want it to say"If BookingTime is greater than 10:00 but less than 16:00 then (VLOOKUP PART) lookup the table and pull off the relevant price, if it's greater than 16:01 but less than 22:00 then lookup table and pull off relevant price etc etc. Now i know i'm not speaking english!!! B

First set up a 2-column list like this one:

{0.417361111111111,2;
0.667361111111111,3;
0.834027777777778,4}

The longish looking numbers are really times corresponding to;

10:01
16:01
20:01

and small numbers are prices per mile.

Select all cells of this list, go to the Name Box on the Formula Bar, type PriceList, and hit enter.

Put the base charge in some cell, select this cell, and name it BaseCharge, following the foregoing procedure.

You could then use something like this:

=BaseCharge+E1*VLOOKUP(F1,PriceList,2)

where E1 houses a miles reading and F a booking time.
This message was edited by aladin akyurek on 2002-04-27 14:01

 

[Yogi Anand]

OK Beckwa:
Set up a tableof rates and times, say in cells A1:B5 as shown below ...

Time Rate
0:01 1.25
10:01 1.55
16:01 1.85
22:01 1.95

then let us say you want to lookup at a rate that applies to a time frame that's less than 16:01, use the following formula:

=VLOOKUP(timevalue("16:00"),$A$1:$B$5,2,1)
this will give you a rate of 1.55

now let us look at the rate that applies to a time frame 16:01 or later

=VLOOKUP(timevalue("16:01"),$A$1:$B$5,2,1)
this will give you 1.85

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

 

[Beckwa]

Ok, i'm obviousoy doing soemthing wrong, i emailed the workbook to u Yogi, don't know if that will help cos i'm totally lost now!! B

 

[Yogi Anand]

On 2002-04-27 13:56, Beckwa wrote:
Ok, i'm obviousoy doing soemthing wrong, i emailed the workbook to u Yogi, don't know if that will help cos i'm totally lost now!! B

OK Becky:
I have looked at your worksheet ... I had to make some minor adjustments in your worksheet to make it work.
The time function you are using is NOW(), so we dont have to use TIMEVALUE(BookingTime); the other thing I had to was to refere to your Prices Table worksheet. So now in the Booking Form, the formula that you use for Base Charge is now:

=VLOOKUP(NOW(),'Prices Table'!$A$1:$B$5,2,1)

so now with my local time 17:19, the formula results in 2.00 Pounds (since I am in the United States, I don't want to under charge you and state $2.00)

Please adjust your Prices Table values and every other item accordingly. I have only looked at the formula you wanted, and not the make up of the rest of your workbook.

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

 

[Beckwa]

Ok this kinda works but it's still not quite what i want. If I change the time to 13:45 for example - it still says £2.00?!?! If u look at the table it shows that it should say £1.40 as it is after 10:00 and before 16:00. B

 

[Yogi Anand]

On 2002-04-27 14:46, Beckwa wrote:
Ok this kinda works but it's still not quite what i want. If I change the time to 13:45 for example - it still says £2.00?!?! If u look at the table it shows that it should say £1.40 as it is after 10:00 and before 16:00. B

Hi Becky:
For this to work with NOW() function as well as a manual time entry, just change the formula to ...

=VLOOKUP(BookingTime,'Prices Table'!$A$1:$B$5,2,1)

Regards!

Yogi Anand

 

[Beckwa]

There won't be any manual time entry, well hopefully anyway. What i mean is, as a test i changed the time on my computer to 13:45 instead of 22:45. In theory this means the base charge should change to a cheaper rate through the lookup but it didn't!!! Thanks for all ur help, B