Frequency function and open intervals

[nicosdj]

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
[vba]COUNTIF(Sheet1!J$2:J$33;"<425000")-B30[/vba] [vba]B30 = COUNTIF(Sheet1!J$2:J$33;"<400000")-B29 [/vba] 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]

How about

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

[mdmackillop]

Using frequency

[nicosdj]

Thank you for your fast replies. Unfortunately I have to leave work now but I'll look more into your solutions tomorrow.

[nicosdj]

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]

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]

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]

Mdmackillop: I'm sorry but I don't quite understand that. How exactly would the frequency function look?

[mdmackillop]

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]

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]

Because I subtraact all numbers greater than or EQUAL to 425000.

Set a value to exactly 425000 and try it.

[nicosdj]

Here goes. Sorry I didn't do that to begin with...

[Bob Phillips]

No data in the workbook.

[mdmackillop]

Like so?

[nicosdj]

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]

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]

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]

And the frequency formula is an array formula, so much slower.

[nicosdj]

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]

You would have to change the intervals to 424999.99 from 425000 etc.