Hi. I have the following formula in my spreadsheet that I need some help with:

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000=""),Damian !$P$4:$P$10000)),0)

Cells N4 through N10000 contain either a blank cell or an E or an I. Cells
P4 through P10000 contain a calculated number. The array function states the
following:

If any cell within the range of N4 through N10000 on the worksheet named
Damian are blank
then find the average of the corresponding cells within the range of P4
through P10000
and round to the zero digit

I have a similar function (see below) in another cell to find all cells in
N4 through N10000 that are not blank. This formula works fine.

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000<""),Damia n!$P$4:$P$10000)),0)

The formula for finding blank cells is returning a value of 0 when it should
return a value of 21. Any ideas of why this is not working?

Thanks!

Maybe there are some spaces in there. Try

=ROUND(AVERAGE(IF(TRIM(Damian!$N$4:$N$10000)="",Da mian!$P$4:$P$10000)),0)



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Meteor1240" wrote in message
...
Hi. I have the following formula in my spreadsheet that I need some help
with:

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000=""),Damian !$P$4:$P$10000)),0)

Cells N4 through N10000 contain either a blank cell or an E or an I.
Cells
P4 through P10000 contain a calculated number. The array function states
the
following:

If any cell within the range of N4 through N10000 on the worksheet named
Damian are blank
then find the average of the corresponding cells within the range of P4
through P10000
and round to the zero digit

I have a similar function (see below) in another cell to find all cells in
N4 through N10000 that are not blank. This formula works fine.

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000<""),Damia n!$P$4:$P$10000)),0)

The formula for finding blank cells is returning a value of 0 when it
should
return a value of 21. Any ideas of why this is not working?

Thanks!

Nope, didn't work either but thanks for trying.

"Meteor1240" wrote:

Hi. I have the following formula in my spreadsheet that I need some help with:

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000=""),Damian !$P$4:$P$10000)),0)

Cells N4 through N10000 contain either a blank cell or an E or an I. Cells
P4 through P10000 contain a calculated number. The array function states the
following:

If any cell within the range of N4 through N10000 on the worksheet named
Damian are blank
then find the average of the corresponding cells within the range of P4
through P10000
and round to the zero digit

I have a similar function (see below) in another cell to find all cells in
N4 through N10000 that are not blank. This formula works fine.

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000<""),Damia n!$P$4:$P$10000)),0)

The formula for finding blank cells is returning a value of 0 when it should
return a value of 21. Any ideas of why this is not working?

Thanks!

Forgot to mention that I am using the CTRL+SHFT+ENTER keys when entering
these formulas.

"Meteor1240" wrote:

Hi. I have the following formula in my spreadsheet that I need some help with:

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000=""),Damian !$P$4:$P$10000)),0)

Cells N4 through N10000 contain either a blank cell or an E or an I. Cells
P4 through P10000 contain a calculated number. The array function states the
following:

If any cell within the range of N4 through N10000 on the worksheet named
Damian are blank
then find the average of the corresponding cells within the range of P4
through P10000
and round to the zero digit

I have a similar function (see below) in another cell to find all cells in
N4 through N10000 that are not blank. This formula works fine.

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000<""),Damia n!$P$4:$P$10000)),0)

The formula for finding blank cells is returning a value of 0 when it should
return a value of 21. Any ideas of why this is not working?

Thanks!

"Meteor1240" wrote in message
...
=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000=""),Damian !$P$4:$P$10000)),0)

This will return 0's for all cells were N & P are empty, is it possible that
you have enough blank rows to reduce the average below 1?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Meteor1240" wrote in message
...
Hi. I have the following formula in my spreadsheet that I need some help
with:

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000=""),Damian !$P$4:$P$10000)),0)

Cells N4 through N10000 contain either a blank cell or an E or an I.
Cells
P4 through P10000 contain a calculated number. The array function states
the
following:

If any cell within the range of N4 through N10000 on the worksheet named
Damian are blank
then find the average of the corresponding cells within the range of P4
through P10000
and round to the zero digit

I have a similar function (see below) in another cell to find all cells in
N4 through N10000 that are not blank. This formula works fine.

=ROUND(AVERAGE(IF((Damian!$N$4:$N$10000<""),Damia n!$P$4:$P$10000)),0)

The formula for finding blank cells is returning a value of 0 when it
should
return a value of 21. Any ideas of why this is not working?

Thanks!