Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Vlookup dilemna

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!




    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    Northampton, UK
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •