Counta or daveragea functions
 

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.

Counta or daveragea functions
 

Which cells do you wish to average?

In article ,
Katrina C. <Katrina wrote:

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.

Counta or daveragea functions
 

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:A1000,A1:A100))
(again, committed with Ctrl+Shift+Enter)


Regards,
Ryan--
--
RyGuy


"Katrina C." wrote:

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.

Counta or daveragea functions
 

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.

"Domenic" wrote:

Which cells do you wish to average?

In article ,
Katrina C. <Katrina wrote:

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.

Counta or daveragea functions
 

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,A 24,A26,A28,A31,A33,A35,A37,A39,A41,A43,A46,A50,A53 ,A56,A58,A61,A63,A66,A69,A70,A72,A75,A78,A81,A84,A 87,A90,A93,A96,A99,A102,A105,A108,A111,A115,A119,A 123,A127,A131))

that's 51 arguments


--


Regards,


Peo Sjoblom


"Katrina C." wrote in message
...
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.

"Domenic" wrote:

Which cells do you wish to average?

In article ,
Katrina C. <Katrina wrote:

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.

Counta or daveragea functions
 

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.
--
Best regards,
Edward


"Katrina C." wrote:

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.

Counta or daveragea functions
 

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.

In article ,
Katrina C. wrote:

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.

"Domenic" wrote:

Which cells do you wish to average?

In article ,
Katrina C. <Katrina wrote:

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.

Counta or daveragea functions
 

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

Katrina

"Katrina C." wrote:

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.