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

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

--
Biff
Microsoft Excel MVP


"ErikVegas" wrote in message
...
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

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

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

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


.

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


.

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?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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


.

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" wrote:

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


.

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


.



.

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


.



.

=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