frequency function and list features

[Johnny Jebel]

I am trying to look at frequency distributions of results of medical tests. I
have downloaded many thousands of results for approximately 15 different
medical tests to an Excel file.

Following this, I have set up the frequency function to separate the data
into bins, and set up a list so that I can select one test at a time.

Unfortunately, I can't find any way to make the frequency function use just
the data displayed in my list, rather than all rows. Is there any way to
achieve what I want to do?

Many thanks.

 

[Ron Coderre]

See if this example helps:

With A1:B19 containing this list:
Test Value
A 1
A 3
A 5
A 7
A 9
A 11
A 13
A 15
A 17
B 1
B 2
B 3
B 4
B 5
B 6
B 7
B 8
B 9

D1: ( a test to calc frequencies for....eg A )
B1: Freq

And....E1:E5 containing this list:
Bins
5
10
15
20

Try this ARRAY FORMULA:

Select F2:F5, with F2 as the active cell

Enter this formula in F2:
=FREQUENCY(IF($A$2:$A$19=$D$1,B2:B19),E2:E5)
Commit that formula with Ctrl+Shift+Enter (instead of just Enter)

Now cells F2:F5 will display the frequencies for the test referenced in D1.

Using my example:
if D1: A....these values are returned in E1:F5
Bins Freq
5 3
10 2
15 3
20 1

If D1: B...then these are the returned values:
Bins Freq
5 5
10 4
15 0
20 0

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Ron Coderre]

This may be a re-post....My PC did something "funny":

See if this example helps:

With A1:B19 containing this list:
Test Value
A 1
A 3
A 5
A 7
A 9
A 11
A 13
A 15
A 17
B 1
B 2
B 3
B 4
B 5
B 6
B 7
B 8
B 9

D1: ( a test to calc frequencies for....eg A )
B1: Freq

And....E1:E5 containing this list:
Bins
5
10
15
20

Try this ARRAY FORMULA:

Select F2:F5, with F2 as the active cell

Enter this formula in F2:
=FREQUENCY(IF($A$2:$A$19=$D$1,B2:B19),E2:E5)
Commit that formula with Ctrl+Shift+Enter (instead of just Enter)

Now cells F2:F5 will display the frequencies for the test referenced in D1.

Using my example:
if D1: A....these values are returned in E1:F5
Bins Freq
5 3
10 2
15 3
20 1

If D1: B...then these are the returned values:
Bins Freq
5 5
10 4
15 0
20 0

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Ron Coderre]

This may be a re-post. The web post screen keeps freezing up:

See if this example helps:

With A1:B19 containing this list:
Test Value
A 1
A 3
A 5
A 7
A 9
A 11
A 13
A 15
A 17
B 1
B 2
B 3
B 4
B 5
B 6
B 7
B 8
B 9

D1: ( a test to calc frequencies for....eg A )
B1: Freq

And....E1:E5 containing this list:
Bins
5
10
15
20

Try this ARRAY FORMULA:

Select F2:F5, with F2 as the active cell

Enter this formula in F2:
=FREQUENCY(IF($A$2:$A$19=$D$1,B2:B19),E2:E5)
Commit that formula with Ctrl+Shift+Enter (instead of just Enter)

Now cells F2:F5 will display the frequencies for the test referenced in D1.

Using my example:
if D1: A....these values are returned in E1:F5
Bins Freq
5 3
10 2
15 3
20 1

If D1: B...then these are the returned values:
Bins Freq
5 5
10 4
15 0
20 0

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Johnny Jebel]

Hi, Ron,

I finally got this to work with your assistance!

Many thanks!

John

 

[Ron Coderre]

I'm glad I could help......and thanks for the feedback.


***********
Regards,
Ron

XL2003, WinXP