Excel assistance badly needed

[Buz2b]

How do I assign a value (a number like 1-10) to a word (or just a letter) in a spreadsheet and then total them at the end of a column. Example is that in one column I want to assign a value of 1 to the word "Yes" and 2 to the word "No" and 3 to the word "Maybe". I then would want to total that column but..........
Next question: How do I show a relationship between information in a row. Example: If I have row with four columns where a Y, N or M can be placed (only one per row) in the first three columns and then the column after it has a value (similar as in question #1). Now show a relationship (percentage?) of Y's or N's or M's to the value in the last row. I think the statement would be, if you have a "Y" in column 1(of the first three), then XX% of the time you will have a XX in column 4. Then if you have a N in column 1 then you will have an XX in column 4. The same would be for all entries in that row. This information would of course go on another page. God help me I think I am lost.

 

[Workin']

OK, for the 1st question, I think you need to use 2 columns - one for the Y, N, M's and one for assigning the numerical value. So the 1st column would have the YNM, the second would have IF...THEN statements to assign the number, say cell A1 has the letter then cell B1 could have =IF(A1="Y",1,IF(A1="N",2,IF(A1="M",3,))) the syntax might not be exactly like that but very close. Then you can total up the B column.

I think you could use similar logic to extend this to your second question.

 

[Buz2b]

Thanks for the reply. It will take some time to digest your assistance and try it out. It seems to be what I was looking for. I've never gotten this far into Excel before. Just the basic sums and percentages. Charting new territory here for me.

 

[Bglad]

I'll just add another idea.

This can be done using only 3 cells.

I don't know why you need 3 columns for your Y, N and M's. This could be done in one column but... If your list is in columns A-C your formula for the average of Y's would be:
=COUNTIF(A1:C50,"y")/COUNTA(A1:C50)

COUNTIF counts the number of cells with a certain criteria, in this case Y
COUNTA counts the number of cells that are not blank

 

[Buz2b]

Bglad:
That is certainly an interesting thought. I was not familiar with that at all either. Thank you for your input. I will also toy with that idea. So, if I wanted an average of all items; Y's, N's or M's, would I have to have three seperate "answer" cells?

 

[Bglad]

Yes

 

[Buz2b]

<< Yes >>


ThanKs!

 

[Buz2b]

Hopefully I will be able to sneak in a follow up question. How do I track entries in a column for doing an average. In other words, if I want to find out what the average of the total of the entries in a column. Wait, that is confusing. I want to know/track how many entries in a column and then do a percentage of each of three columns to the total entries. Example: Column A has 12 (of a possible 31) entries. Column B has 6 and column C has 13. Now, in a different set of cells I want to know what percentage of my entries were A, B and C. To add to that confusion, is there a way to go further (do it differently) and set it to track a seperate percentage of (for example) Y's, N's or M's in each column; assuming a possibility that there will be a variable # of entries. In other words there may be as few as 5 or 6 entries and up to 31. I promise this will be the last for a bit. (Really, trust me!)

 

[Bglad]

Hopefully I will be able to sneak in a follow up question. How do I track entries in a column for doing an average. In other words, if I want to find out what the average of the total of the entries in a column. Wait, that is confusing. I want to know/track how many entries in a column and then do a percentage of each of three columns to the total entries. Example: Column A has 12 (of a possible 31) entries. Column B has 6 and column C has 13. Now, in a different set of cells I want to know what percentage of my entries were A, B and C.

I don't understand exactly what you want to do. If you need a specific answer, you'll have to be more specific about what you want to do and the variables involved.

If you want the average of three different sized columns I would put your answers in cells A32-C32 if the longest column has a possible number of 31 entries. COUNTA will count how many entries are actually there and always give you the correct avg.

If you want the average of all 3 columns, A-C, put your answer in C32. Or you can merge cells A32-C32 into one cell so that it is under all three rows.

If you are wanting to know the number of entries separate from the average, just split out the formula: cell C32 "=COUNTA(A1:C31)"

In another cell the avg of Y's: cell c33 "=COUNTIF(A1:C50,"y")/C32"
In another cell the avg of N's: cell c34 "=COUNTIF(A1:C50,"n")/C32"

To add to that confusion, is there a way to go further (do it differently) and set it to track a seperate percentage of (for example) Y's, N's or M's in each column; assuming a possibility that there will be a variable # of entries. In other words there may be as few as 5 or 6 entries and up to 31.

I've already given you the formula. This is nothing more than a variable on the theme. I don't think you are understanding how the COUNT functions work. Look up COUNTA and COUNTIF in the Excel help files. You just change the cells it is counting and the variable Y, N or M that it is looking to average. All I'm doing for all of your questions is changing the formula around and changing what cell it is stored in.

 

[Buz2b]

Thank you for your response again. I understand that it is a bit confusing. If I knew exactly how this was going to be constructed I would have more specific questions. The problem is that this document is a work in process and changes from time to time. I/we are considering different ways of correlating the information and trying to figure out the best way to view the data. I appreciate your patience and will do more research on the "count" function. Again, I am sorry if my questions were a bit vague and not entirely clear.
I will "play" with what has been suggested for now. If and when I get down to something more specific I will repost under a new thread. I hope you will give it a look when I do.