Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Countif Function - Calculating Between Two Sets of Values
Hey guys! Hope you can help me - I've been struggling trying to work out what is happening with some of my calculations.
Here is my scenario: I am trying to identify, between a range of cells (in this case, P5:P185 on the separate worksheet RED WET 1), how many of these contain a value less than 5, a value between 5 and 10, a value between 10 and 20, and upwards up until 100, whereby I then want to work out how many cells contain a value of 100 or more. Now for the cells containing a value of less than 5, I've used the following countif formula which works: =COUNTIF('RED WET 1'!P4:P185,"<=5.00") For the cells containing a value of 100 or more, I've used the following countif formula which works: =COUNTIF('RED WET 1'!P4:P185,"100.01") Now, the inbetween values are where I am struggling. For the cells containing a value which lies between 5.01 and 10, I have this formula: =COUNTIF('RED WET 1'!P4:P185,"5.01")-COUNTIF('RED WET 1'!P4:P185,"<10.00") And I used the same formula, but as the value I wanted to find changed, I changed (so instead of "5.01" it would have "10.01" or "20.01"). BUT what has started to happen is that it is returning to me, negative values. I have a feeling that the formulae I am using is calculating the first part, then deducting the second part, meaning that if there is a high number calculated by the second part, it is resulting in a minus and negative number. But I didn't think this would happen seeing as it was COUNTIF and these appeared to be identified ranges. So basically - I tried the different sums - so / and + and - and * but these still just seemed to have different effects and never returned the true number of how many cells contained say, a value greater than 5.01 and less than 10.00. Can someone please help me? I've tried everything from COUNTIF to SUMPRODUCT, and a few other internet suggestions, and I'm beginning to feel like giving up. Surely this can't be as hard as I'm trying to find out? This seemed relatively simple and something that COUNTIF should be able to do, but perhaps I'm just not doing it right? [Background info - the cells contain a distance from a postcode to that location, such as 10.11 miles away, 11.01 miles away, and so I am trying to work out how many cells contain values between certain amounts so that I can then do nifty little charts of how many people live a certain number of miles away from this certain point] |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Countif Function - Calculating Between Two Sets of Values
Hi,
Am Tue, 19 Mar 2013 17:22:21 +0000 schrieb EmzOLV: Now for the cells containing a value of less than 5, I've used the following countif formula which works: =COUNTIF('RED WET 1'!P4:P185,"<=5.00") For the cells containing a value of 100 or more, I've used the following countif formula which works: =COUNTIF('RED WET 1'!P4:P185,"100.01") Now, the inbetween values are where I am struggling. For the cells containing a value which lies between 5.01 and 10, I have this formula: =COUNTIF('RED WET 1'!P4:P185,"5.01")-COUNTIF('RED WET 1'!P4:P185,"<10.00") try: =COUNTIF(P4:P185,"<=10")-COUNTIF(P4:P185,"<=5") or: =SUMPRODUCT(--(P4:P1855),--(P4:P185<=10)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Quote:
You can use the SUMPRODUCT formula below in any Excel version. The COUNTIFS one will only work in Excel 2007 or later. You just need to change the 5 & 10 in these formulas for appropriate numbers for each distance range. You could put these in cells and reference them in the formula so you only have to write the formula once and then copy it. But without seeing your workbook I couldn't tell you exactly what the formula would look like then. =SUMPRODUCT(('RED WET 1'!P4:P1855)*('RED WET 1'!P4:P185<=10)) =COUNTIFS('RED WET 1'!P4:P185,"5",'RED WET 1'!P4:P185,"<=10") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Countif Function - Calculating Between Two Sets of Values
On Tue, 19 Mar 2013 17:22:21 +0000, EmzOLV wrote:
Hey guys! Hope you can help me - I've been struggling trying to work out what is happening with some of my calculations. Here is my scenario: I am trying to identify, between a range of cells (in this case, P5:P185 on the separate worksheet RED WET 1), how many of these contain a value less than 5, a value between 5 and 10, a value between 10 and 20, and upwards up until 100, whereby I then want to work out how many cells contain a value of 100 or more. For a problem like this, I would use the FREQUENCY function. =FREQUENCY(P5:P185,{5,10,20,100}) Enter it as an array from, for example A1:A5 As written, A1: will show the count of values less than or equal to five. A2: will show the count of values greater than five but less than or equal to ten A3: will show the count of values greater than ten but less than or equal to 20. A4: will show the count of values greater than 20 but less than or equal to 100 A5: will show the count of values greater than 100 To enter the formula, first select A1:A5. Enter the formula into the formula bar, then, instead of hitting <ENTER, hold down <CTRL+SHIFT while hitting <ENTER. Excel will copy the same formula into A1:A5 and place curly brackets {...} around the formula. |
#5
|
|||
|
|||
Firstly - THANKS everyone who replied :) I really appreciate the time you took to help me with my issue!
Quote:
I would have put up my spreadsheet but aside from the mileage aspect, a lot of it was confidential :) but thats perfect thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF Function Provides Text Feedback, Not Values | Excel Worksheet Functions | |||
Excel graph 3 sets of data. 2 sets as lines 1 as column? | Charts and Charting in Excel | |||
Excel: Sumif function with two sets of criteria? Can it be done? | Excel Discussion (Misc queries) | |||
How do I apply icon sets to percentage values in Excel worksheet? | Excel Worksheet Functions | |||
countif with two sets of non consectutive ranges at the same time | Excel Worksheet Functions |