how do I count figures between 10 and 15

[Wakster]

I have a column of numbers and wish to count any that come between 5 and 10 (6.2,7,9.5 for example) and if possible any numbers that come between 10 and 15.

Thanks in Advance

Wakster

[Nimrod]

You may want to look at the "Frequency" formula

[Aladin Akyurek]

On 2002-05-25 23:58, Wakster wrote:
I have a column of numbers and wish to count any that come between 5 and 10 (6.2,7,9.5 for example) and if possible any numbers that come between 10 and 15.

Thanks in Advance

Wakster

Lets say that the numbers are in column A.

In D1 enter: 5
In E1 enter: 10
In D2 enter: 10
In E2 enter: 15

In F1 enter and copy down:

=COUNTIF(A:A,">="&D1)-COUNTIF(A:A,">="&E1)

Adjust the ranges to suit. The formula counts numbers between lower limit inclusive and upper limit exclusive.


[ This Message was edited by: Aladin Akyurek on 2002-05-26 01:58 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-26 02:01 ]

[Wakster]

Thank you,

The countif solution worked.

I will also try the frequecy function if i can find out how it works.

[Aladin Akyurek]

On 2002-05-26 00:58, Wakster wrote:
Thank you,

The countif solution worked.

I will also try the frequecy function if i can find out how it works.

To count by using FREQUENCY,

in B1:B4 enter:

{4;9;14;19}

in C1 enter and copy down:

=INDEX(FREQUENCY($A$1:$A$17,$B$1:$B$4),ROW())

The figure below shows this side by side with the COUNTIF version (lower limit inc, upper limit exc, see my edited reply with COUNTIF).

Microsoft Excel - aaFrequencyVsCountif Wakster.xls ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 = 15
	A	B	C	D	E	F
1	15	4	:alert('=INDEX(FREQUENCY($A$1:$A$17,$B$1:$B$4),ROW())')>3	5	10	:alert('=COUNTIF($A$1:$A$17,%22%3E=%22&D1)-COUNTIF($A$1:$A$17,%22%3E=%22&E1)')>7
2	1	9	:alert('=INDEX(FREQUENCY($A$1:$A$17,$B$1:$B$4),ROW())')>7	10	15	:alert('=COUNTIF($A$1:$A$17,%22%3E=%22&D2)-COUNTIF($A$1:$A$17,%22%3E=%22&E2)')>5
3	5	14	:alert('=INDEX(FREQUENCY($A$1:$A$17,$B$1:$B$4),ROW())')>5	15	20	:alert('=COUNTIF($A$1:$A$17,%22%3E=%22&D3)-COUNTIF($A$1:$A$17,%22%3E=%22&E3)')>1
4	6	19	:alert('=INDEX(FREQUENCY($A$1:$A$17,$B$1:$B$4),ROW())')>1
5	3
6	7
7	8
8	5
9	9
10	11
11	10
12	12
13	5
14	2
15	20
16	14
17	12
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Aladin

[Wakster]

Thanks Aladin
The time and trouble you have gone to on my behalf is much appreciated.

Two quick questions if I may,

What does the & symbol in the formula do? and of the two solutions which one would you use?

[Aladin Akyurek]

Two quick questions if I may,

What does the & symbol in the formula do?

& is the concatenate operator that forces Excel to evalutate its AFTER argument and glue the result of the evaluation to its BEFORE argument:

">="

is the BEFORE arg and

D1

the AFTER arg in

">="&D1

The evaluation of D1 simply results in the value that D1 houses. So we get:

">=5"

if D1 houses 5 as value.

If D1 houses a formula like =AVERAGE(F1:F10), the average that is computed will be glued to the BEFORE argument in an expression like

=COUNTIF(A1:A10,">="&AVERAGE(F1:F10))

and of the two solutions which one would you use?

The COUNTIF version will be a bit faster than the FREQUENCY version of the formulas. Moreover, constructing the 'bins' that are required for the FREQUENCY function can be a bit hard if you are not used to it. Otherwise, it won't matter much which function you use.

Aladin