PDA

View Full Version : Frequency function and open intervals



nicosdj
09-23-2009, 05:41 AM
Hi

I'm trying to count how many people that have salaries that are within a certain range. I would like to use the frequency function but I can't figure out how to use open intervals. For instance I have an interval of [400.000 ; 425.000[ (meaning 400.000 is within the interval but 425.000 is not). How do I use the frequency function so that it counts all salaries equal to 400.000 and above 400.000 but below 425.000?

Right now I'm using the countif function as follows
COUNTIF(Sheet1!J$2:J$33;"<425000")-B30 B30 = COUNTIF(Sheet1!J$2:J$33;"<400000")-B29 and so forth so that I extract all numbers below 400.000.
Although the countif function seems as a suitable replacement for the frequency function it appears that it produces wrong results.

Bob Phillips
09-23-2009, 06:22 AM
How about

=COUNTIF(J$2:J$33,">=400000")-COUNTIF(J$2:J$33,">=425000")

mdmackillop
09-23-2009, 06:34 AM
Using frequency

nicosdj
09-23-2009, 07:37 AM
Thank you for your fast replies. Unfortunately I have to leave work now but I'll look more into your solutions tomorrow.

nicosdj
09-24-2009, 03:44 AM
Now I've taken a closer look at both your solutions.

xld: you suggest that I count the instances within the range [400.000 ; eternity[ and then subtract the range [425.000 ; eternity[. I believe the interval I end up with is [400.000 ; 425.000[ which is exactly what I need.

mdmackillop: you suggest using the frequency function which I would prefer so that the users of the sheet easily can understand the function. But isn't it a wrong interval? I think that the frequency function uses this interval ]400.000 ; 425.000] but I need the interval of [400.000 ; 425.000[. From what I gather from your countif function you count the instances within the interval ]eternity ; 425.000] and then subtract the interval ]eternity ; 400.000]. But then you end up with the interval of ]400.000 ; 425.000] instead of [400.000 ; 425.000[.

So I guess my question still is: is there any way that I can use the frequency function when I want to count the lowest number in the interval (400.000) and not the highest number in the interval (425.000)?

mdmackillop
09-24-2009, 03:57 AM
If I understand you correctly.
Change the 425,000 figure to a large number =10^10 or =MAX(B:B). Clear the remaining values.

Bob Phillips
09-24-2009, 04:08 AM
xld: you suggest that I count the instances within the range [400.000 ; eternity[ and then subtract the range [425.000 ; eternity[. I believe the interval I end up with is [400.000 ; 425.000[ which is exactly what I need.

Not quite, it is [400.000 ; 424.999,99[


So I guess my question still is: is there any way that I can use the frequency function when I want to count the lowest number in the interval (400.000) and not the highest number in the interval (425.000)?

Do you mean just the 4000.000 figure, or [400.000 ; 424.999,99[. If the latter, that is what mine does.

nicosdj
09-24-2009, 04:22 AM
Mdmackillop: I'm sorry but I don't quite understand that. How exactly would the frequency function look?

mdmackillop
09-24-2009, 04:31 AM
The interval are entered into a range. They need not be eqally spaced. You could have only 4 values such as 0, 400,000, 425,000 and =Max(B:B)

If you could post a workbook showing your layout and criteria that would clarify things

nicosdj
09-24-2009, 04:36 AM
xld: I think your solution covers the interval I need. But I don't see why it is the [400.000 ; 424.999,99[ interval that is covered - maybe we misunderstand each other because of the brackets and the different ways of defining intervals?

When I type [400.000 ; 425.000[ I define the interval: 400.000=< x <425.000 where x is a salary and I then count the number of x'es that fulfill the condition. The interval can also be written as [400.000 ; 425.000).

Bob Phillips
09-24-2009, 04:38 AM
Because I subtraact all numbers greater than or EQUAL to 425000.

Set a value to exactly 425000 and try it.

nicosdj
09-24-2009, 04:49 AM
Here goes. Sorry I didn't do that to begin with...

Bob Phillips
09-24-2009, 04:55 AM
No data in the workbook.

mdmackillop
09-24-2009, 05:24 AM
Like so?

nicosdj
09-24-2009, 06:34 AM
Thanks to you both!

The frequency function works fine in the sample you've provided mdmackillop (although I don't understand why).
In the attached workbook I've looked at your countif function and tested with a "TÆL.HVISER" (don't know the english version) function as well. The numbers are all equal so that's good - I just wish I understood exactly how it worked.


I should have done it in vba - it would have been so much simpler :)


Thanks again!

Bob Phillips
09-24-2009, 07:24 AM
In the attached workbook I've looked at your countif function and tested with a "TÆL.HVISER" (don't know the english version) ...

TÆL.HVIS is COUNTIF so I assume TÆL.HVISER is 2007 COUNTIFS.

Bob Phillips
09-24-2009, 07:28 AM
The frequency formula looks wrong to me, 425000 gets added to the wrong set. Change 610155 to 425000 and you will see 425000 go up to 80 whereas COUNTIF and COUNTIFS changes 45000 to 84.

Bob Phillips
09-24-2009, 07:32 AM
And the frequency formula is an array formula, so much slower.

nicosdj
09-24-2009, 07:53 AM
xld: Thanks for checking! You're right - 425.000 does indeed get added to the wrong set. I guess it isn't possible for me to use the frequency function then.

Bob Phillips
09-24-2009, 07:55 AM
You would have to change the intervals to 424999.99 from 425000 etc.

nicosdj
09-24-2009, 08:26 AM
Yeah but unfortunately I also calculate the salaries back to year zero so sometimes I end up with a lot of decimals.

But if I use the following countifs function it shold do the trick right?
TÆL.HVISER(Data;">="&400.000;Data;"<"&425.000)

Bob Phillips
09-24-2009, 08:47 AM
Yep, COUNTIFS works as you want it.

nicosdj
09-28-2009, 12:56 AM
I guess I'll go with that then.


Thanks for your help both of you!