how to count instances between a range in libre office calc 4

statistics libreoffice-calc
pleabargain picture pleabargain · Aug 11, 2013 · Viewed 9k times · Source

I have a range of numbers in a1:80. I want to count the total number of values in a1:a80 that fall between X and Y e.g.

Value X = 30000 value Y = 35000

cell a1 = 34000 cell a42 = 33000

Goal: The formula will tell me that there are 2(two) instances in a1:a80 that have a value between X and Y.

I've tried a variety of combos as below:

=COUNTIF(OR($A1:$A80,"<="&V4)$a1$a80,">="&V3)

and

=IF(OR(">="&V3,"<="&W3),"there are COUNT(1)","there are COUNT(0)")

Here's the video: http://www.youtube.com/watch?v=SKKmlSENNLM

and here's the spreadsheet: https://plus.google.com/116948076340592829238/posts/6dDtKuEebNm

Thank you for considering this problem worthy of your time.

Answer

DaN picture DaN · Aug 13, 2013

You may use COUNTIFS, the multi-range, multi-criterion alternative to COUNTIF:

=COUNTIFS(A1:A80;">="&$V$3;A1:A80;"<="&$V$4)

Related questions

Calculating arithmetic mean (one type of average) in Python

Is there a built-in or standard library method in Python to calculate the arithmetic mean (one type of average) of a list of numbers?

Read More
How to normalize an array in NumPy?

I would like to have the norm of one NumPy array. More specifically, I am looking for an equivalent version of this function def normalize(v): norm = np.linalg.norm(v) if norm == 0: return v return v / norm Is there …

Read More
Calculating Pearson correlation and significance in Python

I am looking for a function that takes as input two lists, and returns the Pearson correlation, and the significance of the correlation.

Read More