Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
Click to expand...

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
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.
 
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.
Click to expand...

But previously you said:

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.
Click to expand...

But also:
each load is on one line accross
Click to expand...

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.
Click to expand...

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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.
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Actually I do not have any formulas. this is why I want to create my own.
The percentage for the fuel surcharrge increases by 1% each .083 or .084
We started from 1.953 to 2.037 .084=2%
We ended with 4.287 to 4.370 ? %= 30%
Note:1.953=cost per gallon , %=fuel surcharge
This is as far I can go with it.
 
Hi Millie,

You still need a table of fuel Cost per gallon and percentage, (or a way of
calculating the percentage from the fuel cost but as you say it is *each
..083 or .084* it is obviously not quite consistent), so that we can arrive
at the percentage to enter.

Send me an e-mail, (does not have to be a spreadsheet), so that I can get
your address and I will send you a demonstration sheet with formulas and
explanations. DO NOT post your address here or you will be targeted by the
spam bots

As it says change the part of my address after the @ to: tiscali.co.uk

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I agree with what you said earlier: Gord has probably offered the easiest
(best?) way to deal with this. The table would permit Millie to 'stagger'
increases at .083 or .084 or other breaks as required. I tried responding
to her OP when it first appeared and that was going to be my
recommendation - a VLOOKUP() based on a table on another sheet. But the
system ate my response.

However, since we have a variable value (.083 OR .084) to deal with, she's
probably going to need another column in her sheet to specify the cost per
gallon so that the lookup can be done accurately. If the breaks were
constant at one value (.083 only, or .084 only) then you could do it by
formula alone.
 
Another thing that I was considering was with the OP saying:

and the fact that the data that the OP provided seemed to be the results of
unrounded calculations, (whereby the SUM() was off by 2 cents), was whether
the OP's son had being doing the calculations in VBA or perhaps hidden on a
protected sheet.

It may also be that the Surcharge percentage is not being calculated but
manually entered from another table and thus Millie does not realise the
reason for the asking for details.

I did consider posting formulas to return the results that were show but
thought that if my layout was not the same as Millie's then it may confuse
her more or even worse return wrong results.

Perhaps Millie will post back and enlighten us

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top