M
Margie Campbell
I have a Gas Statement spreadsheet we use to calculate
our mounthly gas costs. The formulas used are basic,
such as =sum(b4/c4). This does not allow for rounding to
nearest cent/percent, etc. This would be ok if we weren't
then asking Excel to take the result of the formula and
multiply it using the formula (=SUM(I32)*J25. The result
is less than accurate.
What we are trying to accomplish is this:
1. Enter amount & cost of gas into spreadsheet (Unleaded,
Diesel & Premimum del 3x week).
2. The above information is calculated by first dividing
the cost by the number of gallons (this gives us cost per
gallon for each invoice) We then total all invoice
gallons & all invoice dollar amounts & divide these to
get the average price per gallon over a one month period.
3. We then record how many gallons of gas were used by
department (Landscape, Maintenance, Delta, Recycle).
4. We then have a formula to take the amount of gas used
by each department and multiply it by the average monthly
price per gallon.
As you can already tell... we have several formula
results being combined that are less than perfect.
We next calculate the cost of actual amount of gas used
by:
1. Enter daily gas onto a seperate spreadsheet. We
record the tank beginning #, ending # & a formula gives
us the gallons actually pumped that day (again no
averaging or rounding, just basic addition, subtraction.
2. We then enter the daily total for gas as written in by
employees & have a basic formula to tell us the
difference between the gas used & the gas reported.
3. The gas used is reported for each department
(Landscape, Maintenance, Delta & Recycle). We have a
formula to give us the Total number of gallons for each
department & the percentage of the total gallons reported
that each department used. Again this comes from a basic
formula without rounding/averaging.
We take the percentage and multiply it by the total sum
of Unleaded, Prem or Diesel used by each department.
This gives us the cost of gas used during the month by
department.
Once each department & type of gas is recorded, we then
transfer the totals to another cell for calculation by
using a simple sum formula =sum(a4,c4)etc.. again without
rounding or averaging. We then have to manually correct
the difference to make the result match the monthly
statement from the gas provider.
The formulations are very basic & combining these makes
for great differences in our totals.
Another problem with using average or round, is that we
don't always have an amount in every column on each
invoice. Need to know how to use average or round
without having it include the "0" columns.
Willing to email the two excel 2000 files to anyone who
wants to take a look.
Thanks in advance.
Marge Campbell
our mounthly gas costs. The formulas used are basic,
such as =sum(b4/c4). This does not allow for rounding to
nearest cent/percent, etc. This would be ok if we weren't
then asking Excel to take the result of the formula and
multiply it using the formula (=SUM(I32)*J25. The result
is less than accurate.
What we are trying to accomplish is this:
1. Enter amount & cost of gas into spreadsheet (Unleaded,
Diesel & Premimum del 3x week).
2. The above information is calculated by first dividing
the cost by the number of gallons (this gives us cost per
gallon for each invoice) We then total all invoice
gallons & all invoice dollar amounts & divide these to
get the average price per gallon over a one month period.
3. We then record how many gallons of gas were used by
department (Landscape, Maintenance, Delta, Recycle).
4. We then have a formula to take the amount of gas used
by each department and multiply it by the average monthly
price per gallon.
As you can already tell... we have several formula
results being combined that are less than perfect.
We next calculate the cost of actual amount of gas used
by:
1. Enter daily gas onto a seperate spreadsheet. We
record the tank beginning #, ending # & a formula gives
us the gallons actually pumped that day (again no
averaging or rounding, just basic addition, subtraction.
2. We then enter the daily total for gas as written in by
employees & have a basic formula to tell us the
difference between the gas used & the gas reported.
3. The gas used is reported for each department
(Landscape, Maintenance, Delta & Recycle). We have a
formula to give us the Total number of gallons for each
department & the percentage of the total gallons reported
that each department used. Again this comes from a basic
formula without rounding/averaging.
We take the percentage and multiply it by the total sum
of Unleaded, Prem or Diesel used by each department.
This gives us the cost of gas used during the month by
department.
Once each department & type of gas is recorded, we then
transfer the totals to another cell for calculation by
using a simple sum formula =sum(a4,c4)etc.. again without
rounding or averaging. We then have to manually correct
the difference to make the result match the monthly
statement from the gas provider.
The formulations are very basic & combining these makes
for great differences in our totals.
Another problem with using average or round, is that we
don't always have an amount in every column on each
invoice. Need to know how to use average or round
without having it include the "0" columns.
Willing to email the two excel 2000 files to anyone who
wants to take a look.
Thanks in advance.
Marge Campbell