Trying to average the contents of a cell across multiple worksheet

  • Thread starter Thread starter ErikVegas
  • Start date Start date
E

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
 
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)
 
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
 
the Frequency function does not seem to
count my negative numbers.
Click to expand...

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?
 
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?
 
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
 
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
 
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))
Click to expand...

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.
 
=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))
 
Back
Top