Max, Frequency, If, Countifs......and somewhere in between

Nintapper · Dec 26, 2017

Hi there,
Tried and tried but can´t get what I need.
My data in A looks something like this:
0.2
0.0
-0.7
3.6
-0.2
8.6
0.3
0.0
-1.1
5.0
-5.1
-3.2
11.1
18.1
-2.70
And the "bin ranges" look like this:
-3.00+
-2.90 to -2.00
-1.90 to -1.00
-0.90 to 0.90
1.00 to 1.90
2.00 to 2.90
3.00+

And I need to calculate the highest consecutive number for each "bin range". So the results should look like this:
-3.00+ =2
-2.90 to -2.00 =1
-1.90 to -1.00 =1
-0.90 to 0.90 =3
1.00 to 1.90 =0
2.00 to 2.90 =0
3.00+ =2

Many thanks

Marcelo Branco · Dec 26, 2017

Welcome to Mr Excel forum

Maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A

[/td][td="bgcolor: #DCE6F1"]

B

[/td][td="bgcolor: #DCE6F1"]

C

[/td][td="bgcolor: #DCE6F1"]

D

[/td][td="bgcolor: #DCE6F1"]

E

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1

[/td][td]

Numbers

[/td][td][/td][td]

Lower

[/td][td]

Higher

[/td][td]

MaxConsec

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2

[/td][td]

0,2

[/td][td][/td][td]

-1000000

[/td][td]

-3

[/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3

[/td][td]

0,0

[/td][td][/td][td]

-2,9

[/td][td]

-2

[/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4

[/td][td]

-0,7

[/td][td][/td][td]

-1,9

[/td][td]

-1

[/td][td]

1

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5

[/td][td]

3,6

[/td][td][/td][td]

-0,9

[/td][td]

0,9

[/td][td]

3

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6

[/td][td]

-0,2

[/td][td][/td][td]

1

[/td][td]

1,9

[/td][td]

0

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7

[/td][td]

8,6

[/td][td][/td][td]

2

[/td][td]

2,9

[/td][td]

0

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8

[/td][td]

0,3

[/td][td][/td][td]

3

[/td][td]

1000000

[/td][td]

2

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9

[/td][td]

0,0