MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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