| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set a range to the currently selected cells? | Excel Programming | |||
| Step through cells in a selected range | Excel Programming | |||
| Set Range Using Selected Cells | Excel Programming | |||
| Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
| max/min of a selected range of cells | Excel Programming | |||
Linear Mode
