# Worksheet Function required

H

#### Hammer

If I have the following data available based on fuel prices what formula
would I require if I wished to calculate the correct fuel surcharge based on
the current fuel price.

I.E if the current fuel price is 0.99p pence the based on the range of
surcharges I want the blank cell next to April 10 to calculate the surcharge
to be 6.85%

I hope that makes sense!!

Month Pence Per Litre Average Price Surcharge Pence Per Litre % Surcharge
Apr-08 Â£0.95 0.95 Â£0.81 0.55%
May-08 Â£1.00 0.98 Â£0.82 0.90%
Jun-08 Â£1.01 0.99 Â£0.83 1.25%
Jul-08 Â£1.01 0.99 Â£0.84 1.60%
Aug-08 Â£1.00 0.99 Â£0.85 1.95%
Sep-08 Â£0.99 0.99 Â£0.86 2.30%
Oct-08 Â£0.93 0.98 Â£0.87 2.65%
Nov-08 Â£0.87 0.97 Â£0.88 3.00%
Dec-08 Â£0.83 0.95 Â£0.89 3.35%
Jan-09 Â£0.83 0.94 Â£0.90 3.70%
Feb-09 Â£0.81 0.93 Â£0.91 4.05%
Mar-09 Â£0.78 0.92 Â£0.92 4.40%
Apr-09 Â£0.82 0.91 Â£0.93 4.75%
May-09 Â£0.82 0.90 Â£0.94 5.10%
Jun-09 Â£0.85 0.90 Â£0.95 5.45%
Jul-09 Â£0.84 0.90 Â£0.96 5.80%
Aug-09 Â£0.87 0.89 Â£0.97 6.15%
Sep-09 Â£0.87 0.89 Â£0.98 6.50%
Oct-09 Â£0.89 0.89 Â£0.99 6.85%
Nov-09 Â£0.90 0.89 Â£1.00 7.20%
Dec-09 Â£0.91 0.89 Â£1.01 7.55%
Jan-10 Â£0.92 0.90 Â£1.02 7.90%
Feb-10 Â£0.91 0.90 Â£1.03 8.25%
Mar-10 Â£0.95 0.90
Apr-10 Â£0.99 0.90

See VLOOKUP() in Excel Help.

Looking at your data the surcharge per litre increases by 1pence per month
and the percentage surcharge rises by .35% per month. this would give you a
surcharge of 8.95% in April-10 and the surcharge_per_litre would be Â£1.05 for
that month.

From the figures I can't see how you would expect to arive at 6.85% for April.

If you want a formula to calculate a rise of Â£0.01 per month then in D3
enter the forula =\$D\$2+(ROW()-2)*0.01 and copy down. The Surcharge % in E3
wuld be E2+0.0035 and copy down.

I feel I've missed something perhaps you can explain again.

Peter