Counta or daveragea functions

[Guest]

I am doing a worksheet that needs to count the non-blank cells to average the
information in the cells. I am having a problem because the cells are not so
I can enter a range but must enter each cell individually. Or at least as
far as I know the the Counta and the daveragea functions will only work with
30 values or arguements. I am sure there is a way around this limit. I am
pretty sure of it but do not know how. Could someone help me with this?

I thank any help I get in advance.

 

[Domenic]

Which cells do you wish to average?

 

[Guest]

This function needs to be committed with Ctrl+Shift+Enter:
=AVERAGE(IF(A1:A100<>"",A1:A100))

You may want to play around with this version too:
=AVERAGE(IF(A1:A100>0,A1:A100))
(again, committed with Ctrl+Shift+Enter)


Regards,
Ryan--

 

[Guest]

The problem is that it is some of the cells in a column but not all of them
and when I try to put them in individually I am over my limit of values or
arguements. I did not set this spread up so the columns are not as I would
have them but there are three differented questions in each column with a
numberic rating and the averages are in three cells below the column.
Because it is not the whole column, I believe that I have to enter the cell
locations individually which makes me have too many values for Counta or
daveragea and I believe there is a way around this to give me the average of
the ratings for each question.

I hope that is clear. I am not very good about explaining something. I
would just count the cells but there is alot and this is done on a regular
basis.

Thank you for all the help you can provide.

Katrina C.

 

[Peo Sjoblom]

You can just use some extra parentheses in your formula to get beyond the 30
argument limit

=COUNTA((A1:A3,A6,A8,A10,A12,A14,A16,A18,A20,A22,A24,A26,A28,A31,A33,A35,A37,A39,A41,A43,A46,A50,A53,A56,A58,A61,A63,A66,A69,A70,A72,A75,A78,A81,A84,A87,A90,A93,A96,A99,A102,A105,A108,A111,A115,A119,A123,A127,A131))

that's 51 arguments


--


Regards,


Peo Sjoblom

 

[Guest]

Instead of working with COUNTA and those messy formulas use Daverage and Dsum
with criteria >0 , you might need a helper column depends on your worksheet
setup( you can hide it). Look at those two functions in Excel help after you
read the help it's a trivial task.

 

[Domenic]

If the target cells don't fall into some sort of pattern, you'll need to
use the technique described by Peo. If they do fall into some sort of
pattern, an alternative may be available.

 

[Guest]

I want to thank everyone that answered my question. You were a great help.

Katrina