countif in a string

[parquerd]

I need a routine that will work much as the CountIf function does.

From a webgrading program, I get a CSV file that contains the number o
the answer that a student gave. So it is very easy to analyze usin
CountIF, how many gave answer 1, 2, etc.

However, when I allow multiple responses, the cell contains a string o
numbers: 1, 7. 10, etc. I need to be able to count how many selec
each answer in this situation too.

In other words, I need a Countif that works not just on whether
cell's contents match a value, but on whether the value is contained i
the string that is in the cell.

Ideas

 

[Frank Kabel]

Hi
try something like
=COUNTIF(range,"*1*")

 

[parquerd]

Thanks, Frank!

Seems to work on all the situations I have.

Now I really feel stupid

 

[Frank Kabel]

Hi
don't do

 

[AlfD]

Frank:

Does that work in the situation described where the multiple choice
could generate

a string of numbers: 1, 7. 10, etc..

Would the 1 pick up 10 as well?

Al

 

[parquerd]

Alf,

I didn't think that it would, but when I tried it (both with Excel an
the very nice shareware Spread32), it did

 

[AlfD]

Hi Paquerd

The formula is looking for the single character "1" (or whatever) whc
is fine if that gives you a unique result. If you want to use thi
approach with potentially two characters (as in 10, 21) the you coul
change it to

=COUNTIF(range,"*01*") etc

but your input codes would need to have two characters, too. e.g
02,07,10.

Do you really use more than 9 possible responses? If you do, you coul
maybe look at using letters instead (A - Z gives you 26 to play with).

In that case, substitute A etc for 1 etc in the formula Frank gav
you.

Al

 

[parquerd]

Hi Alf,

I wouldn't usually have anything close to 10 foils, but in this case,
was asking students which chapters of a textbook they had read, ha
found useful, etc. So i had 13 this time around.

I know Frank's routine should have gotten it incorrect with 1 and 1
using *1*, but for whatever reason I got the right count with my test.

I agree that 01 and A-Z would be the way to go, but in this case,
don't get a choice. The program gives me 1,2,etc.

I don't want to massage things too much -- or I'll be tempted to chun
the whole CSV file into a basic program that I might write for thes
occasions.

My next problem is how to I concatenate a series of cells ONLY IF th
cell isn't empty. So far I have an ugly worksheet that does th
concatenation, but operates on all the cells, empty or not -- so i
some spots of the concatenated string I have a string of the characte
I used to separate the cell contents.

Any tricks to concatenating

 

[AlfD]

Hi!

I see your dilemma on the chapter numbers. But I can't see why Frank'
routine worked!!! Might have to ask him.

Concatenation: tell me more:if I can help, I will. And there's a lot o
good people out there who can go a lot further than I can.

Al

 

[parquerd]

Student short-answers are placed in a cell: C1:C25. I need to presen
all these responses in a single cell. So I simply used contenation t
place C1, a comma, C2, a comma, etc. into the cell. But I get a strin
of ",," or worse when I have students who didn't supply an answer t
that item. What I would like is an "If this cell isn't blank, appen
it to this collection of responses."

And, easy as that would be in BASIC, I am not familiar enough wit
Excel to be able to handle it yet.

Does that make sense

 

[Domenic]

Student short-answers are placed in a cell: C1:C25. I need to present
all these responses in a single cell. So I simply used contenation to
place C1, a comma, C2, a comma, etc. into the cell. But I get a string
of ",," or worse when I have students who didn't supply an answer to
that item. What I would like is an "If this cell isn't blank, append
it to this collection of responses."

And, easy as that would be in BASIC, I am not familiar enough with
Excel to be able to handle it yet.

Does that make sense?

Try,

=IF(A1<>"",A1,"")&IF(A2<>"",","&A2,"")&IF(A3<>"",","&A3,"")&IF(A4<>"",","&A4
,"")...

Hope this helps!

 

[parquerd]

Dominic,

I have the empty part down, it is the concatenation part that gets me.
I would really like to concatenate a range (with interleaving commas
instead of having to do it one cell at a time

 

[Domenic]

Dominic,

I have the empty part down, it is the concatenation part that gets me.
I would really like to concatenate a range (with interleaving commas)
instead of having to do it one cell at a time.

I'm not sure I understand. For simplicity sake, assuming that the student
responses are "Yes" and "No" responses, is this what you're looking for:

Column A
1 Y
2 N
3
4 Y
5 N
6 N
7
8 Y

with the results in one cell as follows: Y,N,Y,N,N,Y

If so, then my formula should work. If not, can you provide more details?

 

[parquerd]

Dominic,

NOW I get it! I missed the additive in there.

Thanks

 

[Domenic]

Dominic,

NOW I get it! I missed the additive in there.

Thanks.

You're welcome!