=SUM(left:right!A1)/(FREQUENCY(left:right!A1,-0.000001)+INDEX(FREQUENCY(left:right!A1,0.000001), 2))

Slight tweak that shortens it a few more keystokes:

=SUM(left:right!A1)/(FREQUENCY(left:right!A1,-0.000001)+INDEX(FREQUENCY(left:right!A1,0),2))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I think that my amendment to your formula
works with negative numbers...?
=SUM(left:right!A1)/(INDEX(FREQUENCY(left:right!A1,{-0.000001,0.000001}),1)+INDEX(FREQUENCY(left:right! A1,{-0.000001,0.000001}),3))

That'll work but you can shorten it a bit:

=SUM(left:right!A1)/(FREQUENCY(left:right!A1,-0.000001)+INDEX(FREQUENCY(left:right!A1,0.000001), 2))

The bins all depend on the size of the numbers you're calculating.

--
Biff
Microsoft Excel MVP


"GrazzaJ" wrote in message
...
Hi,

I want to average numbers, some of which are positive and some of which
are
negative.

No number has more than three decimal places.

I need to exclude 0 values.

The numbers are all calculated from other formulae.

I think that my amendment to your formula works with negative numbers...?

=SUM(left:right!A1)/(INDEX(FREQUENCY(left:right!A1,{-0.000001,0.000001}),1)+INDEX(FREQUENCY(left:right! A1,{-0.000001,0.000001}),3))

Thanks,

G



"T. Valko" wrote:

the Frequency function does not seem to
count my negative numbers.

That particular formula is based on there being only positive numbers to
average excluding any 0 values.

So, what exactly do you need to average? Do you need to exclude 0
values?
Are the numbers calculated and the results of other formulas? The
numbers
that are decimals, how many decimal places are there?

--
Biff
Microsoft Excel MVP


"GrazzaJ" wrote in message
...
Hi,

I have a similar requirement, but where the numbers that I am
averaging
could be positive or negative, integer or decimal.

Having a mix of integer or decimal does not seem to be a problem.

However, the Frequency function does not seem to count my negative
numbers.

Any help would be greatly appreciated!!

Many thanks,

Graham

"T. Valko" wrote:

Depending on the distribution of the numbers (will any be -ve, are
they
all
intergers, etc.):

Based on there not being any -ve numbers:

Sheet1 A1 = 57
Sheet2 A1 = 0
Sheet3 A1 = 46

=SUM(Sheet1:Sheet3!A1)/INDEX(FREQUENCY(Sheet1:Sheet3!A1,0),2)

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in
message
...
See:

http://groups.google.com/group/micro...142815f8bf1fea
--
Gary''s Student - gsnu201001


"ErikVegas" wrote:

I have A workbook that contains 26 worksheets. Sheet 26 is a
summary
page
that gives a running total of of the information entered in the
previous
25
pages. I need to be able to do an average of the contents of one
cell
but I
cant have the average include the null cells or zero value cells
from
the
worksheets that have yet to be populated. I have been using this
Aray
formula:
{=AVERAGE(IF('2-20:8-14'!B28<0,'2-20:8-14'!B28))}

but I keep getting #ref errors. What am I doing wrong or do you
have
a
better way of doing this function.

Thanks,

Erik

Thanks


.



.

That's great -thanks for all your help and comments!

"T. Valko" wrote:

=SUM(left:right!A1)/(FREQUENCY(left:right!A1,-0.000001)+INDEX(FREQUENCY(left:right!A1,0.000001), 2))

Slight tweak that shortens it a few more keystokes:

=SUM(left:right!A1)/(FREQUENCY(left:right!A1,-0.000001)+INDEX(FREQUENCY(left:right!A1,0),2))

--
Biff
Microsoft Excel MVP

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GrazzaJ" wrote in message
...
That's great -thanks for all your help and comments!

"T. Valko" wrote:

=SUM(left:right!A1)/(FREQUENCY(left:right!A1,-0.000001)+INDEX(FREQUENCY(left:right!A1,0.000001), 2))

Slight tweak that shortens it a few more keystokes:

=SUM(left:right!A1)/(FREQUENCY(left:right!A1,-0.000001)+INDEX(FREQUENCY(left:right!A1,0),2))

--
Biff
Microsoft Excel MVP