Easy question for excel expert

[Digital2k]

hello, I have a spreadsheet that has a column that I want to show the
average 4 rows of data. The cell that shows the average(C5), only shows the
average if all 4 rows in column A have data. otherwise it displays the error
#Dl/0! If two or only three of the rows in column A have data how can I get
the cell (C5) to display the average regardless if all rows don't have data?
Thanks,
Digital2k

A B C
1 | 6 | 3 | 50% |
2 | 2 | 1 | 50% |
3 | | | |
4 | | | |
5 | | | #Dl/0!|

 

[Joel]

The worksheet function Avarage only gives an error if all the cells are
empty. You can use an if statement to detect the error

=if(iserror(Average(C1:C4)),"",Average(C1:C4))

 

[Chip Pearson]

Try a formula like

=IF(COUNT(C1:C4)=4,AVERAGE(C1:C4),"empty data cell")

Note that the value 0 in a cells is counted by the COUNT function but an
empty cell is not. COUNT counts only numeric entries, not text entries. To
count text entries,use COUNTA.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Bill Renaud]

Your post does not specify what formulas you have in column C, especially
in cells C3 and C4. If these cells are empty, then the AVERAGE function
should work correctly, as is. If you are getting an error value in cell C5,
then you must have an error in a precedent cell somewhere, which you have
not shown.

Assuming that you are using one of the following formulas in cell C1:

C1: =B1/A1
or
C1: =IF(A1=0,0,B1/A1)

....and that you have column C formatted to not display the value if it is
zero or an error, then you might have to write a UDF (user-defined
function) and include it in the formula for cell C5.

C5: =AVERAGE(ValidRange(C1:C4,FALSE))

....where the UDF ValidRange is defined as follows (put in a standard code
module). Set the IncludeZeroValues parameter to TRUE, or FALSE, depending
on whether you want to include 0 values in the calculation or not. Remember
that quite a few Excel functions (AVERAGE, COUNT, etc.) are limited to 30
values (non-contiguous cell areas).

Public Function ValidRange(MyRange As Range, _
IncludeZeroValues As Boolean) As Range

Dim rngCell As Range
Dim rngValid As Range

On Error Resume Next

For Each rngCell In MyRange
If IsEmpty(rngCell) Then GoTo NextCell
If IsError(rngCell) Then GoTo NextCell
If IsNumeric(rngCell) _
Then
'Check to see if rngCell is 0 and whether it should be included.
If Not IncludeZeroValues And rngCell.Value = 0 _
Then
GoTo NextCell
End If

'Add to cells union.
If rngValid Is Nothing _
Then
Set rngValid = rngCell
Else
Set rngValid = Application.Union(rngValid, rngCell)
End If
End If

NextCell:
Next rngCell

Set ValidRange = rngValid
End Function

(I'm using Excel 2000, and couldn't get the SpecialCells method to work
correctly in this situation. It would return ALL cells in MyRange, even if
they were an error value.)

 

[Digital2k]

Thanks to all who replied, I will try these solutions to see what works
best. There are errors in C3 & C4, I didn't show them.
Thanks again,
Digital2k

 

[Digital2k]

OK, Thanks for this explanation, However I'm still not able to get the
results I need..I should get a 50% as the average in cell C5. I tried all
the suggestions but no luck. I added the errors in C3 & C4 to make this a
little more clear.
C1 - C4 = B1/A1, B2/A2 and so on..

A B C
1 | 6 | 3 | 50% |
2 | 2 | 1 | 50% |
3 | | | #Dl/0!|
4 | | | #Dl/0!|
5 | | | #Dl/0!|

Thanks Please help.

 

[Bill Renaud]

Digital2K wrote:
<<I'm still not able to get the results I need..I should get a 50% as the
average in cell C5. I tried all the suggestions but no luck.>>

Did you try the function I gave? It gave the correct answer of 50% when I
developed it (Excel 2000). This function will probably execute slowly if
you have a large number of cells, since it has a For loop in it.