Home |
Search |
Today's Posts |
#1
|
|||
|
|||
text formulas
I have "Y,N,&NA" in one column. In another column off to the right I am
counting those inputs. A total is then calculating those Y(which formulate to be 100%) and N (which formulate to be 0%) and then averaging them. However, I don't want the NA to be part of the average. Meaning, if NA is input, then the count would be NA, instead of 0% (like the N). It throws off the average and I want it excluded from the avg calculation Any thoughts on how to get this to work?? |
#2
|
|||
|
|||
On Mon, 18 Jul 2005 07:06:07 -0700, Rus
wrote: I have "Y,N,&NA" in one column. In another column off to the right I am counting those inputs. A total is then calculating those Y(which formulate to be 100%) and N (which formulate to be 0%) and then averaging them. However, I don't want the NA to be part of the average. Meaning, if NA is input, then the count would be NA, instead of 0% (like the N). It throws off the average and I want it excluded from the avg calculation Any thoughts on how to get this to work?? For the percentage of Y: =COUNTIF(A1:A100,"=Y")/COUNTIF(A1:A100,"<NA") For the percentage of N: =COUNTIF(A1:A100,"=N")/COUNTIF(A1:A100,"<NA") MP- -- "Learning is a behavior that results from consequences." B.F. Skinner |
#3
|
|||
|
|||
That one seems to work. However, it gives me the"#DIV/0" and I need them to
read "NA" in that cell for my average calculation to work. Any more thoughts?? "Mangus Pyke" wrote: On Mon, 18 Jul 2005 07:06:07 -0700, Rus wrote: I have "Y,N,&NA" in one column. In another column off to the right I am counting those inputs. A total is then calculating those Y(which formulate to be 100%) and N (which formulate to be 0%) and then averaging them. However, I don't want the NA to be part of the average. Meaning, if NA is input, then the count would be NA, instead of 0% (like the N). It throws off the average and I want it excluded from the avg calculation Any thoughts on how to get this to work?? For the percentage of Y: =COUNTIF(A1:A100,"=Y")/COUNTIF(A1:A100,"<NA") For the percentage of N: =COUNTIF(A1:A100,"=N")/COUNTIF(A1:A100,"<NA") MP- -- "Learning is a behavior that results from consequences." B.F. Skinner |
#4
|
|||
|
|||
Try:
=COUNTIF(A1:A100,"Y")/(COUNTIF(A1:A100,"Y")+COUNTIF(A1:A100,"N")) Tim C "Rus" wrote in message ... I have "Y,N,&NA" in one column. In another column off to the right I am counting those inputs. A total is then calculating those Y(which formulate to be 100%) and N (which formulate to be 0%) and then averaging them. However, I don't want the NA to be part of the average. Meaning, if NA is input, then the count would be NA, instead of 0% (like the N). It throws off the average and I want it excluded from the avg calculation Any thoughts on how to get this to work?? |
#5
|
|||
|
|||
Or shorter:
=1/(1+COUNTIF(A1:A100,"N")/COUNTIF(A1:A100,"Y")) Tim C "Tim C" wrote: Try: =COUNTIF(A1:A100,"Y")/(COUNTIF(A1:A100,"Y")+COUNTIF(A1:A100,"N")) Tim C "Rus" wrote: I have "Y,N,&NA" in one column. In another column off to the right I am counting those inputs. A total is then calculating those Y(which formulate to be 100%) and N (which formulate to be 0%) and then averaging them. However, I don't want the NA to be part of the average. Meaning, if NA is input, then the count would be NA, instead of 0% (like the N). It throws off the average and I want it excluded from the avg calculation Any thoughts on how to get this to work?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding text in formulas | Excel Worksheet Functions | |||
show text after formulas | Excel Discussion (Misc queries) | |||
HOW DO I CORRECT A CONDITION IN WHICH ALL FORMULAS ACT LIKE TEXT? | Excel Worksheet Functions | |||
Excel formulas to create large blocks of text | Excel Discussion (Misc queries) | |||
I want Excel to allow cells with formulas and unrelated text | Excel Discussion (Misc queries) |