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.
--
Gary''s Student - gsnu201002


"Hammer" wrote:

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

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

"Hammer" wrote:

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