# Thread: amending a formula (xp2003 sp2)

1. ## 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

2. ## Re: amending a formula (xp2003 sp2)

Try entering 25 in D3

3. ## 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

4. ## 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. ## Re: amending a formula (xp2003 sp2)

Thanks for the explanation Hans,

Steve

#### Posting Permissions

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