IF and ISBlank for a selected range of cells
 

Using Excel 207 and want to search in a particular range (A1:A31) to
determine if all cells are blanks, [true] would return a blank, [false] would
sum the range (A1:A31) then divide by number of hits within that range.

A
1 25
2
3 100
4 75
... 25
30 49
31 89

How would I write the formula? Unable to define range names because of the
size of data and updates to date. I tired this, but it doesn't work.

=+IF(ISBLANK(A1:A31),"",SUM(A1:A31)/31)
Thanks

IF and ISBlank for a selected range of cells
 

You can give this a try... I think it is what you want...

=IF(COUNT(A1:A31)=0,0,AVERAGE(A1:A31))
--
HTH...

Jim Thomlinson


"NavyMom" wrote:

Using Excel 207 and want to search in a particular range (A1:A31) to
determine if all cells are blanks, [true] would return a blank, [false] would
sum the range (A1:A31) then divide by number of hits within that range.

A
1 25
2
3 100
4 75
.. 25
30 49
31 89

How would I write the formula? Unable to define range names because of the
size of data and updates to date. I tired this, but it doesn't work.

=+IF(ISBLANK(A1:A31),"",SUM(A1:A31)/31)
Thanks

IF and ISBlank for a selected range of cells
 

You could use =count() to see how many numbers are in the cell.

Then use =average() instead of sum/count.

=if(count(a1:a31)=0,"No numbers",average(a1:a31))

Or if you really wanted:

=if(count(a1:a31)=0,"No numbers",sum(a1:a31)/count(a1:a31))



NavyMom wrote:

Using Excel 207 and want to search in a particular range (A1:A31) to
determine if all cells are blanks, [true] would return a blank, [false] would
sum the range (A1:A31) then divide by number of hits within that range.

A
1 25
2
3 100
4 75
.. 25
30 49
31 89

How would I write the formula? Unable to define range names because of the
size of data and updates to date. I tired this, but it doesn't work.

=+IF(ISBLANK(A1:A31),"",SUM(A1:A31)/31)
Thanks

--

Dave Peterson

IF and ISBlank for a selected range of cells
 

Thanks,
This helps a lot.
NavyMom

"Dave Peterson" wrote:

You could use =count() to see how many numbers are in the cell.

Then use =average() instead of sum/count.

=if(count(a1:a31)=0,"No numbers",average(a1:a31))

Or if you really wanted:

=if(count(a1:a31)=0,"No numbers",sum(a1:a31)/count(a1:a31))



NavyMom wrote:

Using Excel 207 and want to search in a particular range (A1:A31) to
determine if all cells are blanks, [true] would return a blank, [false] would
sum the range (A1:A31) then divide by number of hits within that range.

A
1 25
2
3 100
4 75
.. 25
30 49
31 89

How would I write the formula? Unable to define range names because of the
size of data and updates to date. I tired this, but it doesn't work.

=+IF(ISBLANK(A1:A31),"",SUM(A1:A31)/31)
Thanks

--

Dave Peterson