South Africa Flag logo

South African Skeptics

March 29, 2017, 03:25:44 AM
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
Go to mobile page.
News: Follow saskeptics on twitter.
   
   Skeptic Forum Board Index   Help Forum Rules Search GoogleTagged Login Register Chat Blogroll  
Pages: [1]   Go Down
  Print  
Author Topic:

Excel

 (Read 778 times)
0 Members and 1 Guest are viewing this topic.
Tweefo
Hero Member
*****

Skeptical ability: +9/-0
Offline Offline

Posts: 1435



WWW
« 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 Offline

Posts: 2403


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 Offline

Posts: 1435



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

Thanks
Logged
Tweefo
Hero Member
*****

Skeptical ability: +9/-0
Offline Offline

Posts: 1435



WWW
« 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 Offline

Posts: 2403


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:

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

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

Skeptical ability: +9/-0
Offline Offline

Posts: 1435



WWW
« 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 Offline

Posts: 1435



WWW
« 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 Offline

Posts: 2403


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
  Print  

 
Jump to:  

Powered by SMF 1.1.11 | SMF © 2006-2009, Simple Machines LLC
Page created in 0.372 seconds with 23 sceptic queries.
Google visited last this page February 22, 2017, 16:50:57 PM
Privacy Policy