Re: Average/Round Functions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 08:11:14 +0200

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 

--
Regards
Frank Kabel
Frankfurt, Germany
Margie Campbell wrote:
> 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

Relevant Pages

  • Re: One more note on the 2S vs. 4S top end thing
    ... but I'm using about $20-30 worth of gas every ... weekend in two bikes. ... $3.00 additional for 2 gallons of gas. ... I'm not sure there's that much difference in cost. ...
    (rec.motorcycles.dirt)
  • Average/Round Functions
    ... I have a Gas Statement spreadsheet we use to calculate ... Enter amount & cost of gas into spreadsheet (Unleaded, ... the cost by the number of gallons (this gives us cost per ... for great differences in our totals. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Wondering whats next wrt CNG in Toyota
    ... > You can do like the gas pipeline companies do, run an engine on NG to ... math that you use to justify the cost-up to have a Hybrid car. ... The hybrid package has a cost up of $7000, ... We drive quite a bit - around 100 gallons per month, ...
    (alt.autos.toyota)
  • Re: String trimmer replacement heads
    ... Of course it saves to stock up ones fuel, not so much on money as time. ... One for diesel and one for regular gas. ... During the summer it now cost me around five gallons a week just to mow my ...
    (rec.gardens)
  • Re: String trimmer replacement heads
    ... Of course it saves to stock up ones fuel, not so much on money as time. ... One for diesel and one for regular gas. ... During the summer it now cost me around five gallons a week just to mow my ...
    (rec.gardens)