Div error help

[scott45]

In my spreadsheet I have a formula that when no information is entered it is
blank using "". At the bottom of this column o838 I want to average, this
column. My formula always returns DIV error. I have read through the help and
tried just about everone (it seems) with the same result. I have tried IF's
Is Error's etc still DIV
=IF(O8:O38="","",AVERAGE(O8:O38))-This one returns value error

=IF(Iserror(IF(O8:O38="",0,AVERAGE(O8:O38)-This returns not enough (

Thanks in advance
scott

 

[Chip Pearson]

You will receive at #DIV/0 error if there is no data in O3:O38.
Try the formula below:

=IF(COUNT(O8:O38)=0,"",AVERAGE(O9:O38))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Peo Sjoblom]

You can use an array formula like

=AVERAGE(IF(O8:O38<>"",O8:O38))

entered with ctrl + shift & enter

will not return an error unless all the cells are blank

 

[scott45]

Chip first of all thanks but this is what I did. At the end of this
column(o39) I have the formula to show the MAX number which with no data in
column 08:038 is zero.So I wrote the following formula and the Div error is
gone and i now have 0:0 showing for my answer. It may not be the correct way
to do it but it does work. I am not familar with writing formulas with "" in
them. I like it because the cells are blank but I then have trouble if I want
to get information from those cells I also tried a conditional formating but
in this case I did not like that.. Thanks again I am sure I will have more
questions
=IF(O39=0,0,AVERAGE(O8:O38))