Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    amending a formula (xp2003 sp2)

    Overly wide screenshot made narrower by HansV

    Good Morning

    No matter how much I strip my workbook I cannot get it below 348 kb so I have taken the snapshot below.

    I have been asked to add 2 new 'rate bands' minimum and 0-25 miles, I have managed to sort out and adjust the formula to get the minimum but I cannot seem to get the formulas in N17, O17, P17 and Q17 to look up the new rate band

    The current formula is =HLOOKUP(B16,$D$3:$J$5,3,TRUE) I thought that by changing it to =HLOOKUP(B16,$C$3:$J$5,3,TRUE) it would include the rate in C5 but it does not, any ideas please
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: amending a formula (xp2003 sp2)

    Try entering 25 in D3

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: amending a formula (xp2003 sp2)

    Thanks Hans

    Can you please explain why that worked as I cannot see any reference to those numbers in that formula?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: amending a formula (xp2003 sp2)

    The HLOOKUP function looks up the mileage in the range C3:J3 (originally in D3:J3). If it doesn't find an exact match, it settles for the largest value that is less than the mileage.
    The cells D3 contained 0, so it'll catch all values from 0 up to the next boundary (75 in E3). You now want to catch values from 25 and up, so you must change the 0 to 25.
    C3 contains the absolute lower bound (I'd change it to 0)

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: amending a formula (xp2003 sp2)

    Thanks for the explanation Hans,

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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