Gas calculation

[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

 

[Domenic]

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!

 

[JE McGimpsey]

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.

 

[Tushar Mehta]

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