If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
More help on a current issue
I posted an issue last week and got a great result. I need
to take this a step further, though. I'm copying the string of Text outlining my issue and the formula suggested. In short, the formula works fine. I end up with a column of info saying either Mild, Moderate, etc.. The problem I am having now, I'm trying to do a Count function, to count all of the Milds for instance. A Count If is giving me a Zero, even though there are clearly Milds in the column. Another problem is, in the event all 4 columns are BLANK, the formula is returning Mild as the answer. So I actually need 2 things - alter the formula so that if the 4 columns are Blank, give me an N/A or something along those lines; then I need a way to count my answers. Below is my original post, followed by the solution I got to that part - 100 clients took a survey and had to answer Mild, Moderate, Severe, Maximal. I have these 4 as headings for Columns B,C,D,E. The rows have numbers. Example, client 1 said Mild 5 times, and Severe once. I need a formula that will let me know what their most 'severe' or highest response is. In other words, for client 1, although they said Mild 5 and Severe only 1, Severe ranks 'higher', so their highest response is Severe. If there was a 1 in the Maximal column, my answer needs to read Maximal. Answer: I assume your four headings are in B1:E1 and the first data in B2:E2. =INDEX($B$1:$E$1,MAX((B2:E2"")*(COLUMN (B2:E2)-1))) array-entered (using CTRL+SHIFT+ENTER rather than just ENTER). Then copy down as far as required. |
#2
|
|||
|
|||
More help on a current issue
"lynn" wrote in message
... I posted an issue last week and got a great result. I need to take this a step further, though. I'm copying the string of Text outlining my issue and the formula suggested. In short, the formula works fine. I end up with a column of info saying either Mild, Moderate, etc.. The problem I am having now, I'm trying to do a Count function, to count all of the Milds for instance. A Count If is giving me a Zero, even though there are clearly Milds in the column. Another problem is, in the event all 4 columns are BLANK, the formula is returning Mild as the answer. So I actually need 2 things - alter the formula so that if the 4 columns are Blank, give me an N/A or something along those lines; then I need a way to count my answers. Below is my original post, followed by the solution I got to that part - 100 clients took a survey and had to answer Mild, Moderate, Severe, Maximal. I have these 4 as headings for Columns B,C,D,E. The rows have numbers. Example, client 1 said Mild 5 times, and Severe once. I need a formula that will let me know what their most 'severe' or highest response is. In other words, for client 1, although they said Mild 5 and Severe only 1, Severe ranks 'higher', so their highest response is Severe. If there was a 1 in the Maximal column, my answer needs to read Maximal. Answer: I assume your four headings are in B1:E1 and the first data in B2:E2. =INDEX($B$1:$E$1,MAX((B2:E2"")*(COLUMN (B2:E2)-1))) array-entered (using CTRL+SHIFT+ENTER rather than just ENTER). Then copy down as far as required. When following up something that's already been started, don't start a new thread. Instead, just reply to the answer you received. To return #N/A if all four cells are blank, use: =IF(AND(ISBLANK(B2:E2)),NA(),INDEX($B$1:$E$1,MAX(( B2:E2"")*(COLUMN(B2:E2)- 1)))) (again array-entered). You could replace NA() by something else, say a text string such as "none" (including the quotes), if you prefer. As for counting answers in column F (assuming that's where you've put the formulas), you could use something along the lines: =COUNTIF($F$2:$F$10,B1) or =COUNTIF($F$2:$F$10,"Mild") suitably adjusted for the number of rows in use in column F. |
Thread Tools | |
Display Modes | |
|
|