Help, I need a formula to increase the surcharge on fuel for my company and I
know nothing about this task. for example :
FUEL SURCHARGE INFO.
2.870 - 2.935 13% added to fuel charge and calculated automatically when
the fuel charge is entered and I click "modify invoice details"
2.935 - 3.037 14%
..
..
3.870 - 3.953 25%
I need to contunue to about $5.249.
Can anyone help me?

2.870 - 2.935 13% added to fuel charge and calculated automatically when
the fuel charge is entered and I click "modify invoice details"

Does this mean that you want the fuel price altered in the same cell, ie
with a Macro so that $2.870 becomes $3.243?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in message
...
Help, I need a formula to increase the surcharge on fuel for my company
and I
know nothing about this task. for example :
FUEL SURCHARGE INFO.
2.870 - 2.935 13% added to fuel charge and calculated automatically when
the fuel charge is entered and I click "modify invoice details"
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249.
Can anyone help me?

Ty Sandy M.
No . I want the fuel price that is entered in one cell to determine the
percentage ie 15% and that % will be added to the total cost per load or
subtotal that is in another cell directly beneath it.
In other words say the base price for a load is 9.00 per ton. we formulate
that x the number of tons in the load ,ie 26 tons. Then we gat a subtotal
and formulate a fuel surcharge determined by the cost of fuel, (which
changes) to get a final cost for the load.
Is this at all clear? the basenumber of tons, price per ton, are added to
get the subtotal. Then we add the %surcharge and get the final cost per load
for each load.
All the calculations are done aumatically when I click on the "modify
Invoice " key.
each load is on one line accross and the total of all loads adds up at the
bottoms of the colums on the invoicer. The "virtual office is in Groove',
but only my son has administration privledges and he is out of the country,
so i need to make one for myself.
Can I state it any better for you? Sorry if I am not good at this.

"Sandy Mann" wrote:

2.870 - 2.935 13% added to fuel charge and calculated automatically when
the fuel charge is entered and I click "modify invoice details"

Does this mean that you want the fuel price altered in the same cell, ie
with a Macro so that $2.870 becomes $3.243?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in message
...
Help, I need a formula to increase the surcharge on fuel for my company
and I
know nothing about this task. for example :
FUEL SURCHARGE INFO.
2.870 - 2.935 13% added to fuel charge and calculated automatically when
the fuel charge is entered and I click "modify invoice details"
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249.
Can anyone help me?

Millie,

Gord has already given most likely the simplest way to go

I find that your explanation has actually confused me more. For example:

In other words say the base price for a load is 9.00 per ton.

But previously you said:

FUEL SURCHARGE INFO.
2.870 - 2.935 13%
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249

Where then does, (presumably), $9.00 come in?

Also:

percentage ie 15% and that % will be added to the total cost per load or
subtotal that is in another cell directly beneath it.

But also:

each load is on one line accross

Not that that is any great problem, it just adds to the confusion.

Again:

All the calculations are done aumatically when I click on the "modify
Invoice " key.

Suggests to me that the calculation are actually being done in code not
formulas because formulas calculate automatically unless you have
Calculation set to manual and you are pressing the Function key F9.

Finally, it may be my ignorance but I have no idea what The *"virtual office
is in Groove'* means unless you mean that the sheet is protected. If it is
it may be that your son did not apply a password and if so you could
unprotect it by selecting: Tools Protection Unprotect sheet. If your
son set a password then you will be asked to enter it but if not the sheet
will be unprotected automatically.

Anyway, on to the problem, going back to the something like the original
figures, follow me through with this: In A1:B14 enter the table:

Cost Percentage
2.87 13%
3.05 14%
3.23 15%
3.41 16%
3.59 17%
3.77 18%
3.95 19%
4.13 20%
4.31 21%
4.49 22%
4.67 23%
4.85 24%
5.03 25%

But enter your real figures in your working example.

If the base cost of the fuel is in A20 say 4.04 and a quantity of 1000 in
B20. The subtotal in C20 would have the formula:

=A20*B20 and will return 4040. The surcharge in C20 would be:

=VLOOKUP(A20,A1:B14,2)*B20
and that would return 190

The total cost would then be in D20:

=C20+D20 which is 4230

If I am wide of the mark then do post back and correct me or send me a
private e-mail by changing my address below as it says and I will send you a
sample sheet elaborating on the above.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in message
...


Ty Sandy M.
No . I want the fuel price that is entered in one cell to determine the
percentage ie 15% and that % will be added to the total cost per load or
subtotal that is in another cell directly beneath it.
In other words say the base price for a load is 9.00 per ton. we formulate
that x the number of tons in the load ,ie 26 tons. Then we gat a subtotal
and formulate a fuel surcharge determined by the cost of fuel, (which
changes) to get a final cost for the load.
Is this at all clear? the basenumber of tons, price per ton, are added to
get the subtotal. Then we add the %surcharge and get the final cost per
load
for each load.
All the calculations are done aumatically when I click on the "modify
Invoice " key.
each load is on one line accross and the total of all loads adds up at the
bottoms of the colums on the invoicer. The "virtual office is in Groove',
but only my son has administration privledges and he is out of the
country,
so i need to make one for myself.
Can I state it any better for you? Sorry if I am not good at this.

"Sandy Mann" wrote:

2.870 - 2.935 13% added to fuel charge and calculated automatically
when
the fuel charge is entered and I click "modify invoice details"

Does this mean that you want the fuel price altered in the same cell, ie
with a Macro so that $2.870 becomes $3.243?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in message
...
Help, I need a formula to increase the surcharge on fuel for my company
and I
know nothing about this task. for example :
FUEL SURCHARGE INFO.
2.870 - 2.935 13% added to fuel charge and calculated automatically
when
the fuel charge is entered and I click "modify invoice details"
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249.
Can anyone help me?

Sandy, I would send you a copy of a completed invoice, but rthis is the best
I can come up with .
can't get it all on one line here.

Date Bill of Lading Unit Rate perTon Description Load Weight
Base-Charge
10/29 1051550 $9.15 Cement 26.93
246.41
Fuel Surcharge Total
18%- 44.35 290.76
10/29/2007 1051550 $9.15 Cement 26.93 246.41 18% 44.35 290.76
10/29/2007 1051594 $9.15 Cement 26.98 246.87 18% 44.44 291.30
10/30/2007 1051615 $9.15 Cement 27.02 247.23 18% 44.50 291.73
10/30/2007 1051666 $9.15 Cement 26.64 243.76 18% 43.88 287.63
10/31/2007 1051693 $9.15 Cement 27.12 248.15 18% 44.67 292.81
10/31/2007 1051754 $9.15 Cement 26.65 243.85 18% 43.89 287.74
11/01/2007 1051773 $9.15 Cement 26.91 246.23 18% 44.32 290.55
11/01/2007 1051849 $9.15 Cement 26.98 246.87 18% 44.44 291.30
11/02/2007 1051875 $9.15 Cement 27.05 247.51 18% 44.55 292.06
11/02/2007 1051941 $9.15 Cement 26.6 243.39 18% 43.81 287.20
11/03/2007 1051964 $9.15 Cement 26.75 244.76 18% 44.06 288.82

Subtotal 3,191.92
Tax 0.00
Miscellaneous 0.00
Balance Due 3,191.92

Does this help? I took the first line and stretched it out so you could
read it better,but it wouldn't all fit on one line like it shouold be.

"Sandy Mann" wrote:

Millie,

Gord has already given most likely the simplest way to go

I find that your explanation has actually confused me more. For example:

In other words say the base price for a load is 9.00 per ton.

But previously you said:

FUEL SURCHARGE INFO.
2.870 - 2.935 13%
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249

Where then does, (presumably), $9.00 come in?

Also:

percentage ie 15% and that % will be added to the total cost per load or
subtotal that is in another cell directly beneath it.

But also:

each load is on one line accross

Not that that is any great problem, it just adds to the confusion.

Again:

All the calculations are done aumatically when I click on the "modify
Invoice " key.

Suggests to me that the calculation are actually being done in code not
formulas because formulas calculate automatically unless you have
Calculation set to manual and you are pressing the Function key F9.

Finally, it may be my ignorance but I have no idea what The *"virtual office
is in Groove'* means unless you mean that the sheet is protected. If it is
it may be that your son did not apply a password and if so you could
unprotect it by selecting: Tools Protection Unprotect sheet. If your
son set a password then you will be asked to enter it but if not the sheet
will be unprotected automatically.

Anyway, on to the problem, going back to the something like the original
figures, follow me through with this: In A1:B14 enter the table:

Cost Percentage
2.87 13%
3.05 14%
3.23 15%
3.41 16%
3.59 17%
3.77 18%
3.95 19%
4.13 20%
4.31 21%
4.49 22%
4.67 23%
4.85 24%
5.03 25%

But enter your real figures in your working example.

If the base cost of the fuel is in A20 say 4.04 and a quantity of 1000 in
B20. The subtotal in C20 would have the formula:

=A20*B20 and will return 4040. The surcharge in C20 would be:

=VLOOKUP(A20,A1:B14,2)*B20
and that would return 190

The total cost would then be in D20:

=C20+D20 which is 4230

If I am wide of the mark then do post back and correct me or send me a
private e-mail by changing my address below as it says and I will send you a
sample sheet elaborating on the above.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in message
...


Ty Sandy M.
No . I want the fuel price that is entered in one cell to determine the
percentage ie 15% and that % will be added to the total cost per load or
subtotal that is in another cell directly beneath it.
In other words say the base price for a load is 9.00 per ton. we formulate
that x the number of tons in the load ,ie 26 tons. Then we gat a subtotal
and formulate a fuel surcharge determined by the cost of fuel, (which
changes) to get a final cost for the load.
Is this at all clear? the basenumber of tons, price per ton, are added to
get the subtotal. Then we add the %surcharge and get the final cost per
load
for each load.
All the calculations are done aumatically when I click on the "modify
Invoice " key.
each load is on one line accross and the total of all loads adds up at the
bottoms of the colums on the invoicer. The "virtual office is in Groove',
but only my son has administration privledges and he is out of the
country,
so i need to make one for myself.
Can I state it any better for you? Sorry if I am not good at this.

"Sandy Mann" wrote:

2.870 - 2.935 13% added to fuel charge and calculated automatically
when
the fuel charge is entered and I click "modify invoice details"

Does this mean that you want the fuel price altered in the same cell, ie
with a Macro so that $2.870 becomes $3.243?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in message
...
Help, I need a formula to increase the surcharge on fuel for my company
and I
know nothing about this task. for example :
FUEL SURCHARGE INFO.
2.870 - 2.935 13% added to fuel charge and calculated automatically
when
the fuel charge is entered and I click "modify invoice details"
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249.
Can anyone help me?

Hi Millie,

Well that all seems straightforward enough. The numbers that you show in
the total column add up to 3191.90 so I assume that you must have the
formulas to calculate the Base-Charge, Fuel Surcharge, and Total and that
you have the cells formatted to Number, or Currency with two decimals
showing

Are you asking for a formula to automatically insert the 18% or whatever the
correct percentage is for the load? If so what are the parameters of the
percentages?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in message
...
Sandy, I would send you a copy of a completed invoice, but rthis is the
best
I can come up with .
can't get it all on one line here.

Date Bill of Lading Unit Rate perTon Description Load Weight
Base-Charge
10/29 1051550 $9.15 Cement 26.93
246.41
Fuel Surcharge Total
18%- 44.35 290.76
10/29/2007 1051550 $9.15 Cement 26.93 246.41 18% 44.35 290.76
10/29/2007 1051594 $9.15 Cement 26.98 246.87 18% 44.44 291.30
10/30/2007 1051615 $9.15 Cement 27.02 247.23 18% 44.50 291.73
10/30/2007 1051666 $9.15 Cement 26.64 243.76 18% 43.88 287.63
10/31/2007 1051693 $9.15 Cement 27.12 248.15 18% 44.67 292.81
10/31/2007 1051754 $9.15 Cement 26.65 243.85 18% 43.89 287.74
11/01/2007 1051773 $9.15 Cement 26.91 246.23 18% 44.32 290.55
11/01/2007 1051849 $9.15 Cement 26.98 246.87 18% 44.44 291.30
11/02/2007 1051875 $9.15 Cement 27.05 247.51 18% 44.55 292.06
11/02/2007 1051941 $9.15 Cement 26.6 243.39 18% 43.81 287.20
11/03/2007 1051964 $9.15 Cement 26.75 244.76 18% 44.06 288.82

Subtotal 3,191.92
Tax 0.00
Miscellaneous 0.00
Balance Due 3,191.92

Does this help? I took the first line and stretched it out so you could
read it better,but it wouldn't all fit on one line like it shouold be.

"Sandy Mann" wrote:

Millie,

Gord has already given most likely the simplest way to go

I find that your explanation has actually confused me more. For example:

In other words say the base price for a load is 9.00 per ton.

But previously you said:

FUEL SURCHARGE INFO.
2.870 - 2.935 13%
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249

Where then does, (presumably), $9.00 come in?

Also:

percentage ie 15% and that % will be added to the total cost per load
or
subtotal that is in another cell directly beneath it.

But also:

each load is on one line accross

Not that that is any great problem, it just adds to the confusion.

Again:

All the calculations are done aumatically when I click on the "modify
Invoice " key.

Suggests to me that the calculation are actually being done in code not
formulas because formulas calculate automatically unless you have
Calculation set to manual and you are pressing the Function key F9.

Finally, it may be my ignorance but I have no idea what The *"virtual
office
is in Groove'* means unless you mean that the sheet is protected. If it
is
it may be that your son did not apply a password and if so you could
unprotect it by selecting: Tools Protection Unprotect sheet. If
your
son set a password then you will be asked to enter it but if not the
sheet
will be unprotected automatically.

Anyway, on to the problem, going back to the something like the original
figures, follow me through with this: In A1:B14 enter the table:

Cost Percentage
2.87 13%
3.05 14%
3.23 15%
3.41 16%
3.59 17%
3.77 18%
3.95 19%
4.13 20%
4.31 21%
4.49 22%
4.67 23%
4.85 24%
5.03 25%

But enter your real figures in your working example.

If the base cost of the fuel is in A20 say 4.04 and a quantity of 1000 in
B20. The subtotal in C20 would have the formula:

=A20*B20 and will return 4040. The surcharge in C20 would be:

=VLOOKUP(A20,A1:B14,2)*B20
and that would return 190

The total cost would then be in D20:

=C20+D20 which is 4230

If I am wide of the mark then do post back and correct me or send me a
private e-mail by changing my address below as it says and I will send
you a
sample sheet elaborating on the above.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in message
...


Ty Sandy M.
No . I want the fuel price that is entered in one cell to determine the
percentage ie 15% and that % will be added to the total cost per load
or
subtotal that is in another cell directly beneath it.
In other words say the base price for a load is 9.00 per ton. we
formulate
that x the number of tons in the load ,ie 26 tons. Then we gat a
subtotal
and formulate a fuel surcharge determined by the cost of fuel, (which
changes) to get a final cost for the load.
Is this at all clear? the basenumber of tons, price per ton, are added
to
get the subtotal. Then we add the %surcharge and get the final cost
per
load
for each load.
All the calculations are done aumatically when I click on the "modify
Invoice " key.
each load is on one line accross and the total of all loads adds up at
the
bottoms of the colums on the invoicer. The "virtual office is in
Groove',
but only my son has administration privledges and he is out of the
country,
so i need to make one for myself.
Can I state it any better for you? Sorry if I am not good at this.

"Sandy Mann" wrote:

2.870 - 2.935 13% added to fuel charge and calculated automatically
when
the fuel charge is entered and I click "modify invoice details"

Does this mean that you want the fuel price altered in the same cell,
ie
with a Macro so that $2.870 becomes $3.243?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Millie Dunham" wrote in
message
...
Help, I need a formula to increase the surcharge on fuel for my
company
and I
know nothing about this task. for example :
FUEL SURCHARGE INFO.
2.870 - 2.935 13% added to fuel charge and calculated automatically
when
the fuel charge is entered and I click "modify invoice details"
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249.
Can anyone help me?

You could most likely go with the VLOOKUP function.

You will need a table with fuel charge column and a surcharge % column.

For more on VLOOKUP function see help or Debra Dalgleish's site for good
instructions and a downloadable sample workbook.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

On Mon, 5 Nov 2007 07:18:01 -0800, Millie Dunham
wrote:

Help, I need a formula to increase the surcharge on fuel for my company and I
know nothing about this task. for example :
FUEL SURCHARGE INFO.
2.870 - 2.935 13% added to fuel charge and calculated automatically when
the fuel charge is entered and I click "modify invoice details"
2.935 - 3.037 14%
.
.
3.870 - 3.953 25%
I need to contunue to about $5.249.
Can anyone help me?