South African Skeptics

Excel

Tweefo · 8 · 2580

Offline Tweefo

  • Hero Member
  • *****
    • Posts: 1580
    • Skeptical ability: +10/-0
    • African Sky Planetaruim
What Excel formula do I use, and how do I use it to do the following?: A column of say 150 lines with different percentages. These percentages range from 7 to 96. I need the spreadsheet to count how many cells have between 0 and 9, how many between 10 and 19, how many between 20 and 29 and so on. I tried =DCOUNTA but when I put in <10 it gives an invalid error back.
If you don't agree with me you are obviously not from this planet.


Offline Rigil Kent

  • Armed liberal
  • Hero Member
  • *****
    • Posts: 2542
    • Skeptical ability: +20/-3
  • Three men make a tiger.
=COUNTIF(range;">=0")-COUNTIF(range;">=10")
=COUNTIF(range;">=10")-COUNTIF(range;">=20")
=COUNTIF(range;">=20")-COUNTIF(range;">=30")

etc, etc. where range is your column of data, say $C$5:$C$155
You know it's cold outside when you go outside and it's cold.


Offline Tweefo

  • Hero Member
  • *****
    • Posts: 1580
    • Skeptical ability: +10/-0
    • African Sky Planetaruim
Thanks
If you don't agree with me you are obviously not from this planet.


Offline Tweefo

  • Hero Member
  • *****
    • Posts: 1580
    • Skeptical ability: +10/-0
    • African Sky Planetaruim
Tried that, but it gives the wrong value. Should it not be something like =COUNTIF(A2:A22,">=0"&"<=9") ? Needless to say, my effort also does not work.
If you don't agree with me you are obviously not from this planet.


Offline Rigil Kent

  • Armed liberal
  • Hero Member
  • *****
    • Posts: 2542
    • Skeptical ability: +20/-3
  • Three men make a tiger.
Tried that, but it gives the wrong value.
In what way is it wrong?  Do you want the border values (0, 10, 20, 30, etc ) excluded from the count, or included in the upper count)?

Also, are you sure that you've used a semicolon and not a comma between the range and the criterion? I.e. =COUNTIF(range;">=0")-COUNTIF(range;">=10")

The COUNTIF function allows only one criterion at a time so I doubt =COUNTIF(A2:A22;">=0"&"<=9") will work.

Here is a file you can download and open with Excel. It should illustrate things a bit better:

https://drive.google.com/open?id=0B15XOtoadAdJZVV0OG1MNkFQa2M

Rigil
« Last Edit: May 23, 2016, 16:02:36 pm by Rigil Kent »
You know it's cold outside when you go outside and it's cold.


Offline Tweefo

  • Hero Member
  • *****
    • Posts: 1580
    • Skeptical ability: +10/-0
    • African Sky Planetaruim
You were right I used a comma, but it is still not working. I even copied it from your post but get this pic. To my mind, the 2nd part should be like this, but that also doesn't work. =COUNTIF(A2:A22;">=0")-countif(a2:a22;"<=9")
If you don't agree with me you are obviously not from this planet.


Offline Tweefo

  • Hero Member
  • *****
    • Posts: 1580
    • Skeptical ability: +10/-0
    • African Sky Planetaruim
Now when I copied it from the page you mentioned it works! Thanks. And this is with commas instead of semicolons. Anyway it works, thanks again.
If you don't agree with me you are obviously not from this planet.


Offline Rigil Kent

  • Armed liberal
  • Hero Member
  • *****
    • Posts: 2542
    • Skeptical ability: +20/-3
  • Three men make a tiger.
Good, glad you are sorted. My version of Excel is 2010 ... perhaps you are using a fresher incarnation with slightly different thingymebobs.
You know it's cold outside when you go outside and it's cold.