| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 :) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| =%A%1, is this a good formula in Excel 2000? | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Linear Mode
