Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counta | New Users to Excel | |||
COUNTA | Excel Worksheet Functions | |||
COUNTA AND | Excel Discussion (Misc queries) | |||
counta | Excel Discussion (Misc queries) | |||
COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions |