Excel Formula

[Chris Waller]

Dear All,

I am looking for an Excel formula that could perform quite a complex calculation. I need a formula that could pick numbers from a random list of numbers. First of all the formula should be able to pick out the number of 9s and 10s in the list and calculate them as a percentage of the whole and then find all the 0s to 6s and then calculate them as a percentage of the whole and then deduct one from the other. Can anyone offer any guidance? TIA

 

[AlanY]

with your data in Column A

=((COUNTIF(A:A,"=9")+COUNTIF(A:A,"=10"))-(COUNTIF(A:A,"=0")+COUNTIF(A:A,"=6")))/COUNT(A:A)

 

[Chris Waller]

with your data in Column A

=((COUNTIF(A:A,"=9")+COUNTIF(A:A,"=10"))-(COUNTIF(A:A,"=0")+COUNTIF(A:A,"=6")))/COUNT(A:A)

Alan,

Thanks for the quick reply. The formula came in very handy and works a treat. Could I ask one supplementary question, and I don't know if this will happen. I have omitted the numbers 7 and 8 as pointed out in my earlier post. If I need to exclude these when I am calculating the percentage, how would I go about that? TIA

 

[AlanY]

Alan,

Thanks for the quick reply. The formula came in very handy and works a treat. Could I ask one supplementary question, and I don't know if this will happen. I have omitted the numbers 7 and 8 as pointed out in my earlier post. If I need to exclude these when I am calculating the percentage, how would I go about that? TIA

COUNT(A:A) gives the total in column A, and
COUNTIF(A:A,"=7") the total number of 7s in column A

thus total in column A to excl 7s and 8s will be COUNT(A:A) - (COUNTIF(A:A,"=7") + COUNTIF(A:A,"=8"))

therefore your formula will be

=((COUNTIF(A:A,"=9")+COUNTIF(A:A,"=10"))-(COUNTIF(A:A,"=0")+COUNTIF(A:A,"=6"))-(COUNTIF(A:A,"=7")+COUNTIF(A:A,"=8")))/(COUNT(A:A)-(COUNTIF(A:A,"=7")+COUNTIF(A:A,"=8")))