Having Excel calculate the number of times a range of values occur

[Rodney]

I am wanting to have Excel be able to calculate the number of times that a
value within a certain range of values occur in my spreadsheet. For
instance: I am looking to see how many times a patient's blood levels of a
drug was between 10 - 20. Each time the data is collected it will be a
number with one decimal point.

Drug level on date x Number of values between 1-10
5 4
10
22
15.1
8
9.3
10.2
18.3
25
36

Thanks for any help you can offer.

 

[Claus Busch]

Hi Rodney,

Am Fri, 16 Oct 2009 15:56:01 -0700 schrieb Rodney:

I am wanting to have Excel be able to calculate the number of times that a
value within a certain range of values occur in my spreadsheet. For
instance: I am looking to see how many times a patient's blood levels of a
drug was between 10 - 20. Each time the data is collected it will be a
number with one decimal point.

Drug level on date x Number of values between 1-10
5 4
10
22
15.1
8
9.3
10.2
18.3
25
36

for your example and values >=10 and <=20:
=COUNTIF(A2:A11,"<=20")-COUNTIF(A2:A11,"<10")
or
=SUMPRODUCT(--(A2:A11>=10),--(A2:A11<=20))


Mit freundlichen Grüssen
Claus Busch

 

[Ron@Buy]

Assuming your data is in column A enter either of these formulae somewhere in
your spreadsheet:
=COUNTIFS(A1:A100">10",A1:A100,"<20")
or
=SUMPRODUCT(--(A1:A100>10),--(A1:A100<20))
Adjust range to suit
Hope this helps