1

I have following column /1/, what I want to calcuate the occurrences of range,for example [1,2),[2,3),[3,4),[4,max) in the example/1/, the occurrences of [1,2) is 1, and [2,3) is 2.... How to write excel formular?

/1/
Weight
1.1
2.2
3.1
4.2
2.1
3.2
4.5
5.1
user84592
  • 4,750
  • 11
  • 55
  • 91

1 Answers1

1

I used a combination of IF, AND, LT/LTE and GT/GTE to get the following demo in Google Sheets. You can change the values in Range Min and Range Max to see the results. The relevant formula is something like:

=IF(AND(GTE(val,range_low),LT(val,range_high)),1,0)

Then just sum them up!

If you wish to change between closed/open intervals you can just switch LT for LTE and GT for GTE as necessary.

aardvarkk
  • 14,955
  • 7
  • 67
  • 96
  • I will say this is a feasible way, but not that handy. Note, google has a little bit different grammar from microsoft, I immitate the demo version: =IF(AND($A2>=0,$A2<1),1,0,0,0). I give the score, but expect better answer. – user84592 Nov 23 '16 at 05:28
  • @user84592 What would constitute a "handy" solution? In my eyes, this solution does exactly what you asked. If you need something else, you should specify it in the question. – aardvarkk Nov 23 '16 at 05:34
  • I would like to have a one formular to calculate the sum, now one need first to apply the formular to the row, then sum. I just guess, (Sorry it is subjective), there maybe a better way. Actually in fact, your solution might be the best solution already. May I keep it open still for a couple of days? – user84592 Nov 23 '16 at 06:06
  • @user84592 Of course -- keep it open if you don't think it's answered. But I think what you need to do is to think about exactly what a "correct" solution looks like to you and make that a part of your question. For instance, it seems that maybe you want to calculate the number of occurrences for multiple ranges at the same time. But you haven't specified that in the question, so it's unclear to me what you want the solution to look like. It's a spreadsheet, so perhaps you could think about what that sheet would look like. – aardvarkk Nov 23 '16 at 15:07
  • so far it is the best answer. Finally I found I really need is density distribution plot(Gaussian curve). But with the formular you give, I really can analyze some data. I will show in this page later when I have free time. – user84592 Nov 29 '16 at 01:40