data validation question

Hello,

I'm new to Excel and I've just figured out how to use Data Validation to create these handy dropdown boxes, at any rate here is my question:

Is there a formula that I can use that takes the information from a column (or row) that has data validation boxes w/ either a "yes" or "no" option and have it return the % of "yes" and "no" answers.

I'm not sure if that makes any sense, but...if have a list of 72 items and in columns C1-C72 have the data validation boxes in them, and 40 are selected as "yes" and 32 are selected as "no", is there a formula I can use to have box C73 tell me the % of 'yes" answers and box C74 tell me the % of "no" answers.

Any help would be greatly appreciated.

Kind regards,

Try:

=COUNTIF($C$1:$C$72,"yes")/COUNTA($C$1:$C$72)

and

=COUNTIF($C$1:$C$72,"no")/COUNTA($C$1:$C$72)

You're welcome!

You can use data validation for that too!

select the range next to the range that has the yes/no dropdown.

let's say as in your first example, this range is D1:D27.

After selecting the range go to Data|Validation and choose Custom from the Allow menu. In the formula field enter: =C1="yes"

where C1 is the top most cell in the yes/no range.

Now if the user selects "no" in C1 and tries to type something in D2, he will get an error message.....

Note: this error message is editable to your taste via the other tabs in the Data Validation dialogue box.

NBVC, you're my Excel hero! Thank you again! Two last questions...well until I think of the next one.

First, if a person enters "yes" in cell C1 (which will allow input in D1), can I make D1 a drop down box as well, or am i only allowed 1 data validation per cell. It would be very helpful if I could make C1 a dropdown "yes or no" and then let D1 be a dropdown box be "strawberry, rocky road, other" (i don't want them to be able to put in Mint)...see what I mean?

Second, the spreadsheet I'm creating will have varied data from month to month (I'm making a spreadsheet to track if cancer patients go on a clinical trial or not). The number of patients the office sees varies from month to month. What'd I'd like is the totals to be on the last line on the sheet and what I'd like to avoid is 'X' amount of blank lines before getting to the totals. For instance:

In January we saw 50 patients and 15 of them went on a clinical trial. So, I can use your handy formula on lines 51 and 52. If in Feb the office sees 30 patients, I'd rather not have 20 blank lines before it gets to the totals. March we saw 65 patients, I'd rather not reformat that spreadsheet from month to month.

Does what I'm asking make sense? Is it possible?

Without getting into some VBA macros to find the last row and insert formula, you can't do that too easily.

My suggestion would be to insert a row at the top of the sheet and place the formulas there that will summarize your data.


My previous formula(s) could be ammended to consider entire columns,

e.g. =COUNTIF($C:$C,"yes")/COUNTA($C:$C)

will count all yeses in column C and divided by number of cells containing any values in column C.... you would just subtract, from the divisor, the number of rows at top that contain headings, etc. so that you only account for the rows containing yes or no entries.

yes, you can do that


First create the list somewhere in the spreadsheet in a single column.

Then select that list and go to Insert|Name|Define and in the field at the top (names in workbood) enter the word "yes" (without quotes). Click OK

Now select any blank cell (that won't ever be used) and repeat the naming process above calling that cell "no"....again no quotes.

Now select D1 (cell next to your yes/no dropdown) and go to Data|Validation and select List. In the source field enter formula: =INDIRECT(C1) and click Ok.

Now, when user selects yes in C1, he gets a list of flavours in D1. If he selects no in C1, he gets a blank list.

No problem.

Good Night and Good luck.