Gas calculation

  • Thread starter Thread starter LaughingFrog
  • Start date Start date
L

LaughingFrog

Hello Group

I need a bit of help to make my car expense sheet work

I have a a colomn that has the Liters i fill on the car, one that has the
kilometers i drive on that amount, and a colomn that calculates the average
km/L

Heres the problem. I want it to calculate the total average km/L, but using
the formulas in the excel help doesnt work. I have been trying to just take
=average(G2:G50) but it comes up with an error here, which i think comes
from the fact that not all the rows are filled in yet, and there fore = 0.
Also there are a few DIV/0 slots in the colomn.( any way to make these
disapear, so i can have the formulas entered, and awaiting data?)

Any help would be greatly apreciated.

Yours Truly

Mikael
Denmark
 
Hi Mikael,

Try the following array formula, which needs to be entered using
CONTROL+SHIFT+ENTER:

=AVERAGE(IF(ISNUMBER(G2:G50),G2:G50))

Hope this helps!
 
The error isn't from the blank cells - AVERAGE() ignores them.

AVERAGE() *will* throw an error, however, if there are errors in the
range.

One way to get rid of the #DIV/0 error is, instead of

=A1/B1

to use

=IF(B1<>0,A1/B1,"")

which returns a null string ("") if the denominator = 0. AVERAGE()
ignores text, so as long as you have at least 1 numeric value in the
column , it should calculate correctly.
 
In addition to the comments of the others, if column G contains the
individual average calculations, then average(G2:G50) will *not* yield
the global average. You need to get that number as =sum(kilometers
column) / sum(liters column)

Here's a simple example.

Fill in 1 liter and drive 10 km.

Next time, fill in 2 liters and drive 30 km.

The average of average will give 12.5 km/l. But that is clearly
incorrect!

The sum()/sum() will give 13.33 km/l. This is correct.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top