calculate Average Formula

how do I Calculate the average of numbers not in a contiguous row or column,
excluding cells that have 0?

(The column starts at F5 but I will continuosly add to the f column, so it
doesn't have an ending column.)

It's a simple worksheet calculating my MPG on my car, so I will keep adding
to it.

Try something like this:

=AVERAGE(IF(F5:F1000>0,F5:F1000))
this is an array formula so enter using Ctrl+Shift+Enter


HTH

Jean-Guy

"sross002" wrote:

> how do I Calculate the average of numbers not in a contiguous row or column,
> excluding cells that have 0?
>
> (The column starts at F5 but I will continuosly add to the f column, so it
> doesn't have an ending column.)
>
> It's a simple worksheet calculating my MPG on my car, so I will keep adding
> to it.

that worked great!!!
The formula reads like this: =AVERAGE(IF(F5:F1000>0,F5:F1000))

How would I go about rounding it two decimal places?



"pinmaster" wrote:

> Try something like this:
>
> =AVERAGE(IF(F5:F1000>0,F5:F1000))
> this is an array formula so enter using Ctrl+Shift+Enter
>
>
> HTH
>
> Jean-Guy
>
> "sross002" wrote:
>
> > how do I Calculate the average of numbers not in a contiguous row or column,
> > excluding cells that have 0?
> >
> > (The column starts at F5 but I will continuosly add to the f column, so it
> > doesn't have an ending column.)
> >
> > It's a simple worksheet calculating my MPG on my car, so I will keep adding
> > to it.

=ROUND(AVERAGE(IF(F5:F1000>0,F5:F1000)),2)

still array-entered.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"sross002" <sross002@discussions.microsoft.com> wrote in message
news:0B41FECC-FC94-47AF-930F-26C9CAC6DEBE@microsoft.com...
> that worked great!!!
> The formula reads like this: =AVERAGE(IF(F5:F1000>0,F5:F1000))
>
> How would I go about rounding it two decimal places?
>
>
>
> "pinmaster" wrote:
>
> > Try something like this:
> >
> > =AVERAGE(IF(F5:F1000>0,F5:F1000))
> > this is an array formula so enter using Ctrl+Shift+Enter
> >
> >
> > HTH
> >
> > Jean-Guy
> >
> > "sross002" wrote:
> >
> > > how do I Calculate the average of numbers not in a contiguous row or
column,
> > > excluding cells that have 0?
> > >
> > > (The column starts at F5 but I will continuosly add to the f column,
so it
> > > doesn't have an ending column.)
> > >
> > > It's a simple worksheet calculating my MPG on my car, so I will keep
adding
> > > to it.