Just when you think you've got a good formula.... I need helpwith
 

Well, just when you think it's safe to go in the water.....
I used this formula: =100-(COUNTIF(C8:AF8,"No")*10)
and thought it worked great but then discovered some people
used "N" for no and some typed out "No" for no.So it worked for
NO but not for N.
How do I make it work for
both?
I tried using "if" and "or" in there a couple of different ways, but I'm
doing
something wrong. Excel keeps yelling
at me :)

Just when you think you've got a good formula.... I need helpwith
 

If you want "hits" on entries of "N" and "no", but not on "not this one"

Try this:

=100-SUM(COUNTIF(C8:AF8,{"No","N"})*10)

Otherwise....this one matches anything beginning with "n"
=100-COUNTIF(C8:AF8,"N*")*10

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

Well, just when you think it's safe to go in the water.....
I used this formula: =100-(COUNTIF(C8:AF8,"No")*10)
and thought it worked great but then discovered some people
used "N" for no and some typed out "No" for no.So it worked for
NO but not for N.
How do I make it work for
both?
I tried using "if" and "or" in there a couple of different ways, but I'm
doing
something wrong. Excel keeps yelling
at me :)

Just when you think you've got a good formula.... I need helpw
 

I'm sorry, I put in the incorrect formula that I'm using now!! It should have
been:
=IF(COUNTA(C8:AF8)0,100-(COUNTIF(C8:AF8,"n")*10), "") when the cells are
empty (a blank form) I don't
want the cell with the formula to have "100" in it. I want it to be blank
until
something is intered in the other cells.
I thought from your formula that I could make the "n" in the above formula,
{"n","no"} but it didn't seem to work. Did I do something wrong?


"Ron Coderre" wrote:

If you want "hits" on entries of "N" and "no", but not on "not this one"

Try this:

=100-SUM(COUNTIF(C8:AF8,{"No","N"})*10)

Otherwise....this one matches anything beginning with "n"
=100-COUNTIF(C8:AF8,"N*")*10

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

Well, just when you think it's safe to go in the water.....
I used this formula: =100-(COUNTIF(C8:AF8,"No")*10)
and thought it worked great but then discovered some people
used "N" for no and some typed out "No" for no.So it worked for
NO but not for N.
How do I make it work for
both?
I tried using "if" and "or" in there a couple of different ways, but I'm
doing
something wrong. Excel keeps yelling
at me :)

Just when you think you've got a good formula.... I need helpw
 

Copy this formula and paste it into your sheet:

=IF(COUNTA(C8:AF8)0,100-SUM(COUNTIF(C8:AF8,{"n","no"})*10), "")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

I'm sorry, I put in the incorrect formula that I'm using now!! It should have
been:
=IF(COUNTA(C8:AF8)0,100-(COUNTIF(C8:AF8,"n")*10), "") when the cells are
empty (a blank form) I don't
want the cell with the formula to have "100" in it. I want it to be blank
until
something is intered in the other cells.
I thought from your formula that I could make the "n" in the above formula,
{"n","no"} but it didn't seem to work. Did I do something wrong?


"Ron Coderre" wrote:

If you want "hits" on entries of "N" and "no", but not on "not this one"

Try this:

=100-SUM(COUNTIF(C8:AF8,{"No","N"})*10)

Otherwise....this one matches anything beginning with "n"
=100-COUNTIF(C8:AF8,"N*")*10

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

Well, just when you think it's safe to go in the water.....
I used this formula: =100-(COUNTIF(C8:AF8,"No")*10)
and thought it worked great but then discovered some people
used "N" for no and some typed out "No" for no.So it worked for
NO but not for N.
How do I make it work for
both?
I tried using "if" and "or" in there a couple of different ways, but I'm
doing
something wrong. Excel keeps yelling
at me :)

Just when you think you've got a good formula.... I need helpw
 

Or....did you need this.....
=IF(SUM(COUNTIF(C8:AF8,{"n","no"}))0,100-SUM(COUNTIF(C8:AF8,{"n","no"})*10), "")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Copy this formula and paste it into your sheet:

=IF(COUNTA(C8:AF8)0,100-SUM(COUNTIF(C8:AF8,{"n","no"})*10), "")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

I'm sorry, I put in the incorrect formula that I'm using now!! It should have
been:
=IF(COUNTA(C8:AF8)0,100-(COUNTIF(C8:AF8,"n")*10), "") when the cells are
empty (a blank form) I don't
want the cell with the formula to have "100" in it. I want it to be blank
until
something is intered in the other cells.
I thought from your formula that I could make the "n" in the above formula,
{"n","no"} but it didn't seem to work. Did I do something wrong?


"Ron Coderre" wrote:

If you want "hits" on entries of "N" and "no", but not on "not this one"

Try this:

=100-SUM(COUNTIF(C8:AF8,{"No","N"})*10)

Otherwise....this one matches anything beginning with "n"
=100-COUNTIF(C8:AF8,"N*")*10

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

Well, just when you think it's safe to go in the water.....
I used this formula: =100-(COUNTIF(C8:AF8,"No")*10)
and thought it worked great but then discovered some people
used "N" for no and some typed out "No" for no.So it worked for
NO but not for N.
How do I make it work for
both?
I tried using "if" and "or" in there a couple of different ways, but I'm
doing
something wrong. Excel keeps yelling
at me :)

Just when you think you've got a good formula.... I need helpw
 

This is PERFECT, Ron. Thank you so much!!
You're wonderful!
I was trying to do something
like that, but apparently I was leaving some little tidbit out!!!
I love this place <sigh :D

"Ron Coderre" wrote:

Copy this formula and paste it into your sheet:

=IF(COUNTA(C8:AF8)0,100-SUM(COUNTIF(C8:AF8,{"n","no"})*10), "")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

I'm sorry, I put in the incorrect formula that I'm using now!! It should have
been:
=IF(COUNTA(C8:AF8)0,100-(COUNTIF(C8:AF8,"n")*10), "") when the cells are
empty (a blank form) I don't
want the cell with the formula to have "100" in it. I want it to be blank
until
something is intered in the other cells.
I thought from your formula that I could make the "n" in the above formula,
{"n","no"} but it didn't seem to work. Did I do something wrong?


"Ron Coderre" wrote:

If you want "hits" on entries of "N" and "no", but not on "not this one"

Try this:

=100-SUM(COUNTIF(C8:AF8,{"No","N"})*10)

Otherwise....this one matches anything beginning with "n"
=100-COUNTIF(C8:AF8,"N*")*10

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

Well, just when you think it's safe to go in the water.....
I used this formula: =100-(COUNTIF(C8:AF8,"No")*10)
and thought it worked great but then discovered some people
used "N" for no and some typed out "No" for no.So it worked for
NO but not for N.
How do I make it work for
both?
I tried using "if" and "or" in there a couple of different ways, but I'm
doing
something wrong. Excel keeps yelling
at me :)

Just when you think you've got a good formula.... I need helpw
 

Maybe you can explain something to me, or maybe it's just one of those things
you just have to "know" but...
I can read this formula and it makes sense except for the "") on the end.
I'm not sure what that's for??
Thanks, Meenie :)

"Ron Coderre" wrote:

Copy this formula and paste it into your sheet:

=IF(COUNTA(C8:AF8)0,100-SUM(COUNTIF(C8:AF8,{"n","no"})*10), "")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

I'm sorry, I put in the incorrect formula that I'm using now!! It should have
been:
=IF(COUNTA(C8:AF8)0,100-(COUNTIF(C8:AF8,"n")*10), "") when the cells are
empty (a blank form) I don't
want the cell with the formula to have "100" in it. I want it to be blank
until
something is intered in the other cells.
I thought from your formula that I could make the "n" in the above formula,
{"n","no"} but it didn't seem to work. Did I do something wrong?


"Ron Coderre" wrote:

If you want "hits" on entries of "N" and "no", but not on "not this one"

Try this:

=100-SUM(COUNTIF(C8:AF8,{"No","N"})*10)

Otherwise....this one matches anything beginning with "n"
=100-COUNTIF(C8:AF8,"N*")*10

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

Well, just when you think it's safe to go in the water.....
I used this formula: =100-(COUNTIF(C8:AF8,"No")*10)
and thought it worked great but then discovered some people
used "N" for no and some typed out "No" for no.So it worked for
NO but not for N.
How do I make it work for
both?
I tried using "if" and "or" in there a couple of different ways, but I'm
doing
something wrong. Excel keeps yelling
at me :)

Just when you think you've got a good formula.... I need helpw
 

The dbl-quote returns an empty text string.....without it, the formula will
return FALSE!

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

Maybe you can explain something to me, or maybe it's just one of those things
you just have to "know" but...
I can read this formula and it makes sense except for the "") on the end.
I'm not sure what that's for??
Thanks, Meenie :)

"Ron Coderre" wrote:

Copy this formula and paste it into your sheet:

=IF(COUNTA(C8:AF8)0,100-SUM(COUNTIF(C8:AF8,{"n","no"})*10), "")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

I'm sorry, I put in the incorrect formula that I'm using now!! It should have
been:
=IF(COUNTA(C8:AF8)0,100-(COUNTIF(C8:AF8,"n")*10), "") when the cells are
empty (a blank form) I don't
want the cell with the formula to have "100" in it. I want it to be blank
until
something is intered in the other cells.
I thought from your formula that I could make the "n" in the above formula,
{"n","no"} but it didn't seem to work. Did I do something wrong?


"Ron Coderre" wrote:

If you want "hits" on entries of "N" and "no", but not on "not this one"

Try this:

=100-SUM(COUNTIF(C8:AF8,{"No","N"})*10)

Otherwise....this one matches anything beginning with "n"
=100-COUNTIF(C8:AF8,"N*")*10

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Meenie" wrote:

Well, just when you think it's safe to go in the water.....
I used this formula: =100-(COUNTIF(C8:AF8,"No")*10)
and thought it worked great but then discovered some people
used "N" for no and some typed out "No" for no.So it worked for
NO but not for N.
How do I make it work for
both?
I tried using "if" and "or" in there a couple of different ways, but I'm
doing
something wrong. Excel keeps yelling
at me :)