Average/Round Functions

[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

 

[Frank Kabel]

Hi
you may take a look at
http://www.cpearson.com/excel/rounding.htm

In general the display of the result is only a DISPLAY. The underlying
figure could have more significant digits. If you add theses values you
seem to get 'errors'. To avoid these round you formulas.

In addtion for your example formulas like
=SUM(B4/C4)
the SUM function is not required. Simply use
=B4/C4

 

[Guest]

I am aware that the sum function is not needed in some of
the formulas I gave. I didn't set up this sheet, so was
giving the formulas as they are on the sheet...
My problem is in, as you say, the automatically rounded
display of numbers...

What we need to know is how to adjust the formulas, or
redesign the sheet so that the true values are shown in
the final computations.
For example:
Once we have taken the cost of gas for all (up to 8
entries) gas invoices and had the total of these in cell
g25($2.01) as an average.... we then need to take this
average and multiply it by a manually entered number
(1560.5) of gallons to get the cost of the gas for that
department. When we multiply the number of gallons by
price [sum=(g25*d32)] we get an incorrect amount as the
entry shown in cell d34. The answer should be $3136.61,
but we get $3130.99. This is a difference of $5.62. This
is too large a difference.

What we need is a way to have Excel keep the averages
intact while displaying the shortened amounts.

We are losing too much in the translation.

I can email the spreadsheet & other sheets we use to
calculate our gas usage if that would help.
Meanwhile.. I will work on this... I need the practice
anyways.
Thanks to any who can help with this.

 

[Frank Kabel]

Hi
one way
- Either include a ROUND function for your sub-results. So something
like
=ROUND(G25*value,2)
using this will make the display (if you format it to show two
decimals) and the 'real' value equal. So the end result should be the
same as you would get if you calculate it manually.

What wonders me is the quite large difference in your example. this
should not happen with your example data. If you like you can email me
your file and I'll have a look at it
frank[dot]kabel[at]freenet[dot]de


--
Regards
Frank Kabel
Frankfurt, Germany

I am aware that the sum function is not needed in some of
the formulas I gave. I didn't set up this sheet, so was
giving the formulas as they are on the sheet...
My problem is in, as you say, the automatically rounded
display of numbers...

What we need to know is how to adjust the formulas, or
redesign the sheet so that the true values are shown in
the final computations.
For example:
Once we have taken the cost of gas for all (up to 8
entries) gas invoices and had the total of these in cell
g25($2.01) as an average.... we then need to take this
average and multiply it by a manually entered number
(1560.5) of gallons to get the cost of the gas for that
department. When we multiply the number of gallons by
price [sum=(g25*d32)] we get an incorrect amount as the
entry shown in cell d34. The answer should be $3136.61,
but we get $3130.99. This is a difference of $5.62. This
is too large a difference.

What we need is a way to have Excel keep the averages
intact while displaying the shortened amounts.

We are losing too much in the translation.

I can email the spreadsheet & other sheets we use to
calculate our gas usage if that would help.
Meanwhile.. I will work on this... I need the practice
anyways.
Thanks to any who can help with this.

-----Original Message-----
Hi
you may take a look at
http://www.cpearson.com/excel/rounding.htm

In general the display of the result is only a DISPLAY. The
underlying figure could have more significant digits. If you add
theses values you seem to get 'errors'. To avoid these round you
formulas.

In addtion for your example formulas like
=SUM(B4/C4)
the SUM function is not required. Simply use
=B4/C4