Trying to average the contents of a cell across multiple worksheet

[ErikVegas]

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

 

[T. Valko]

Will the values to average *always* be positive numbers?

 

[Gary''s Student]

See:

http://groups.google.com/group/micr...=gst&q=average+across+sheets#29142815f8bf1fea

 

[T. Valko]

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)

 

[GrazzaJ]

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]

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?

 

[T. Valko]

It may be easier to understand what you want to average if you posted a
small representative sample of the numbers you're dealing with.

What negative number is closest to 0?

 

[GrazzaJ]

Ok, attempt 1 seems to work:

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

Is there a less clunky way of expressing a very small negative number and a
very small positive number than +/- 0.000001?

Again, many thanks!

Graham

 

[GrazzaJ]

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]

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.

 

[T. Valko]

=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))

 

[GrazzaJ]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!