COUNTIF Formula HELP!!!!
Posted by George on March 06, 2001 6:42 AM
Hi,
I am trying to use the following formula:
=COUNTIF(B2:B31,"<=42")/COUNT(B2:B31)
Although as the number of cells will change on a daily basis, I need the user to highlight the cells and then use that range of cells in the formula.
Any ideas on how to do this? maybe a macro?
Thanks
George
Posted by Dave Hawley on March 06, 2001 6:48 AM
Hi George
You could use a macro for this, but I think you should consider the use of a dynamic named range. If you follow my link to my web site, you will see a link for "Dynamic Named Ranges". There are some examples and instructions for them here.
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on March 06, 2001 7:52 AM
Activate the option Insert|Name|Define, type DataRange or what suits you best as name for Names in Workbool and the following formula for Refers to:
=OFFSET(x!$B$2,0,0,COUNTA(x!$B:$B),1)
where x is the name of the sheet whose column B you use for data entry.
Click OK. DataRange is a dynamic range, meaning that you can add/remove data points to column B at will. You must have in column B nothing but user-data which you want to average (in a certain way).
I would suggest in addition that you put the number in a cell, say in C2, select this cell, and
name this cell, e.g., Crit via the Name Box. Now you can rewrite your formula for averaging:
=IF(COUNT(DataRange)>0,COUNTIF(DataRange,">"&Crit)/COUNT(DataRange),"")
Aladin
