## South African Skeptics

October 17, 2018, 15:48:47 PM
 News: Please read the posting guidelines before posting. Entire Forum This board This topic Members Google Entire Site
 Skeptic Forum Board Index Help Forum Rules Search GoogleTagged Login Register Chat Blogroll
 Pages: [1]   Go Down
Author Topic:

# Excel

0 Members and 1 Guest are viewing this topic.
Tweefo
Hero Member

Skeptical ability: +9/-0
Offline

Posts: 1506

 « on: May 21, 2016, 21:34:14 PM »

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.
 Logged
Rigil Kent
Clotting Factor
Hero Member

Skeptical ability: +19/-3
Offline

Posts: 2440

Three men make a tiger.

 « Reply #1 on: May 22, 2016, 03:35:39 AM »

=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
 Logged
Tweefo
Hero Member

Skeptical ability: +9/-0
Offline

Posts: 1506

 « Reply #2 on: May 22, 2016, 12:44:27 PM »

Thanks
 Logged
Tweefo
Hero Member

Skeptical ability: +9/-0
Offline

Posts: 1506

 « Reply #3 on: May 23, 2016, 13:47:27 PM »

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.
 Logged
Rigil Kent
Clotting Factor
Hero Member

Skeptical ability: +19/-3
Offline

Posts: 2440

Three men make a tiger.

 « Reply #4 on: May 23, 2016, 15:25:21 PM »

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:

Rigil
 « Last Edit: May 23, 2016, 16:02:36 PM by Rigil Kent » Logged
Tweefo
Hero Member

Skeptical ability: +9/-0
Offline

Posts: 1506

 « Reply #5 on: May 23, 2016, 17:00:26 PM »

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")
 Logged
Tweefo
Hero Member

Skeptical ability: +9/-0
Offline

Posts: 1506

 « Reply #6 on: May 23, 2016, 17:17:29 PM »

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.
 Logged
Rigil Kent
Clotting Factor
Hero Member

Skeptical ability: +19/-3
Offline

Posts: 2440

Three men make a tiger.

 « Reply #7 on: May 23, 2016, 17:28:52 PM »

Good, glad you are sorted. My version of Excel is 2010 ... perhaps you are using a fresher incarnation with slightly different thingymebobs.
 Logged
 Pages: [1]   Go Up
 « previous next »