+ Reply to Thread
Results 1 to 13 of 13

Count syntax for dividing # of "positive numbers" by "total numbers"

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Count syntax for dividing # of "positive numbers" by "total numbers"

    There are numbers, both positive and negative digits, and some zeroes, in A2:A22.

    The percentage of positive numbers needs to appear in C2.

    I was thinking it would be a count function, using something like "Count("positive numbers"(A2:A22)/count(A2:A22)"...?

    Thanks for any help you might be able to offer...

    Gratefully,
    Synthia
    Gratefully,
    Synthia

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    Hi Synthia,

    Try this:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    This should work:

    =(SUMIF(A:A,">0",A:A)/COUNT(A:A))*100 this gives the sum of the positive numbers divided by the total number of numbers multiplied by 100. Zeros are not being counted as positive though they are counted as a number.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    Thanks, but I don't think I explained it well.

    Explanation: the numbers represent performance of schools.

    I want to see what percent of schools improved.(not concerned at this point with how much of an improvement occurred, just concerned about counting how many schools did not score zero or a negative number, and showing that number as a percentage).

    So, if the number is zero, or negative, I don't want to include that in the "improved" percentage.
    If there are 100 schools, 10 made "zero" improvement, 40 made negative improvement, and 50 made positive gains (improved) the answer would be 50%.

    Thanks for you

  5. #5
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    Thanks,
    Does that eliminate the zero being included with the positive numbers? As I explained later (below) I don't think I explained it well...I need the value to show what percentage of the numbers are not zero or negative numbers... to state the percentage of numbers that are positive numbers...?
    Please pardon if yours does that, I am still playing with it on the sheet, I am grateful for your help.
    Synthia

  6. #6
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    I used your suggestions, thank you, to get the formula:

    HTML Code: 
    =COUNTIF(A2:A22,">0")/COUNT(A2:A22)
    THANKS!
    Synthia

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    Use this instead: This counts the positive numbers (numbers that are greater than zero) and divides that by the total number of numbers and gives the result as a percentage. Zeros count as a number and are not counted as positive.

    =(COUNTIF(A:A,">0")/COUNT(A:A))*100
    Last edited by newdoverman; 06-18-2013 at 07:51 PM.

  8. #8
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    THANK YOU SO MUCH! ACCURACY COUNTS! And you ensured it.
    Thank you,
    Synthia

  9. #9
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    Hi,
    I need to expand it to include other districts, that have 2 line headers in between, so I used:
    HTML Code: 
    =(COUNTIFS(E6:E21,">0",E24:E38,">0",E41:E47,">0"))/((COUNT(E6:E21,E24:E38,E41:E47)))
    because the numbers are in E6:E21, AND E24:E38, AND E41:E47, that I want to evaluate to see the percentage of those numbers that are positive.

    Obviously I don't know what I am doing because I get a value error.
    I took off the *100 because I use the percentage format.

    More help would be greatly appreciated.
    Humbly AND gratefully,
    Synthia

    I wasn't sure if I was supposed to enter a new thread or just mark it unsolved and hope someone would come back to this..?
    Synthia
    Last edited by synses; 06-18-2013 at 08:47 PM.

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will give you the percentage of POSITIVE numbers (zeros and negatives counted only in the total number of numbers). If there is nothing in the rows separating the ranges of values, then the second half of the formula can be shortened like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This may be shortened even further depending upon what is in column E:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by newdoverman; 06-18-2013 at 09:05 PM.

  11. #11
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    Thanks, I'll try the first one, there are other numbers in the rows in between...that is where the averages are, actually. of the scores themselves. This is an analysis of the school performance through a different lens.
    Thanks, it will just take a couple of minutes. I appreciate you helping again!
    Synthia

  12. #12
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    PERFECT !!! THANKS SO MUCH ...MORE!!!
    HTML Code: 
    =((COUNTIF(E6:E21,">0")+COUNTIF(E24:E38,">0")+COUNTIF(E41:E47,">0"))/(COUNT(E6:E21)+COUNT(E24:E38)+COUNT(E41:E47)))
    Synthia

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count syntax for dividing # of "positive numbers" by "total numbers"

    I made a typo in the first range in the formulae, which I have corrected.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1