MODE FORMULA FOR MULTIPLE CONDITIONS??

[michellesssme]

Based on the Data column,
May I know the formula for both conditions (1. & 2.) by using MODE & IF Formula?
1. mode value for condition <650
2. mode value for condition >=650

3. May I know how to insert condition (<650) in the formula (=TEXT(MIN($N:$N),"0")&"~"&TEXT(MAX($N:$N),"0")?
4. How to write the formula to count how many data points fall on the specific range like from 600 to 700?

Data
666
702
741
678
673
707
737
729
797
731
799
729
710
655
608
628
658
643
646
666
628
636
641
677
626
646

 

[KRice]

Have a look and let me know if this answers your questions. I am not quite clear on what is meant by #3, but I interpreted it to mean that you want the min and max of data satisfying the condition <650. The blue cells are user inputs...you may hardwire these values directly into the formulas if desired.

MrExcel_20220803.xlsx
	N	O	P	Q	R
1	Data
2	666		Mode data <	650	628
3	702		Mode data >=	650	666
4	741		Min~Max		608~799
5	678		Min~Max of data < 650		608~646
6	673
7	707		Lower endpoint of range	600
8	737		Upper endpoint of range	700
9	729		Count of points in range		16
10	797		Count of points		26
11	731
12	799
13	729
14	710
15	655
16	608
17	628
18	658
19	643
20	646
21	666
22	628
23	636
24	641
25	677
26	626
27	646
Sheet9

Cell Formulas
Range	Formula
R2	R2	=MODE.SNGL(FILTER($N$2:$N$27,$N$2:$N$27<Q2))
R3	R3	=MODE.SNGL(FILTER($N$2:$N$27,$N$2:$N$27>=Q3))
R4	R4	=TEXT(MIN($N:$N),"0")&"~"&TEXT(MAX($N:$N),"0")
R5	R5	=TEXT(MIN(FILTER(N:N,N:N<650)),"0")&"~"&TEXT(MAX(FILTER(N:N,N:N<650)),"0")
R9	R9	=COUNTIFS(N:N,">="&Q7,N:N,"<="&Q8)
R10	R10	=COUNT(N:N)

 

[RobP]

Hi, I approached it like this:

=MODE(FILTER(B:B,B:B>650)) (you can use <650 also for the other range.)

To find how many in each data array =COUNT(FILTER(B:B,B:B<650))

Is this what you need ?

Rob

 

[RobP]

or there is this one, which looks for numbers > 2, and <3 (for example)

=LET( x,FILTER(B:B,B:B>2),y,FILTER(B:B,B:B<3),cntx,COUNT(x),cnty,COUNT(y),MODE(x) & "/" & cntx &" max "&MAX(x)&" min"&MIN(x)&"  - " &MODE(y)& "/" & cnty&" max:"&MAX(y)&" min:"&MIN(y))

it will output one cell like 4/10 max 5 min 3 - 2/10 max:2 min:1

so mode of the >2 was 4 with data points = 10 max was 5 min was 3 - mode of <3 was 2 / out of 10 numbers max:2 min:1

It might show you what you could do further.

Rgds
Rob

 

[michellesssme]

Have a look and let me know if this answers your questions. I am not quite clear on what is meant by #3, but I interpreted it to mean that you want the min and max of data satisfying the condition <650. The blue cells are user inputs...you may hardwire these values directly into the formulas if desired.

MrExcel_20220803.xlsx
	N	O	P	Q	R
1	Data
2	666		Mode data <	650	628
3	702		Mode data >=	650	666
4	741		Min~Max		608~799
5	678		Min~Max of data < 650		608~646
6	673
7	707		Lower endpoint of range	600
8	737		Upper endpoint of range	700
9	729		Count of points in range		16
10	797		Count of points		26
11	731
12	799
13	729
14	710
15	655
16	608
17	628
18	658
19	643
20	646
21	666
22	628
23	636
24	641
25	677
26	626
27	646
Sheet9

Cell Formulas
Range	Formula
R2	R2	=MODE.SNGL(FILTER($N$2:$N$27,$N$2:$N$27<Q2))
R3	R3	=MODE.SNGL(FILTER($N$2:$N$27,$N$2:$N$27>=Q3))
R4	R4	=TEXT(MIN($N:$N),"0")&"~"&TEXT(MAX($N:$N),"0")
R5	R5	=TEXT(MIN(FILTER(N:N,N:N<650)),"0")&"~"&TEXT(MAX(FILTER(N:N,N:N<650)),"0")
R9	R9	=COUNTIFS(N:N,">="&Q7,N:N,"<="&Q8)
R10	R10	=COUNT(N:N)

Hi, KRice. Thank you for the provided formula. I encountered an error for this formula. May I know what is the reason cause this happen?

 

[michellesssme]

May I know what is the formula to get the range for the yellow area?

 

[RobP]

Hi again,

so if I imagine the user has typed "650~700" into Cell B3 - you can use below formula:

Cell C3 (data points calculation)

=LET(dash,FIND("~",B3,1),lowx,NUMBERVALUE(LEFT(B3,dash-1)),highx,NUMBERVALUE(MID(B3,dash+1,LEN(B3))),nbpnts,COUNT(FILTER(N:N,(N:N>=lowx) *( N:N<=highx))),nbpnts)

(this will dynamically pick up the data in B3 , whether two digits (23~45) or 5 digits (10000~20000)

Then to output the text in the way you wish in D3 (your yellow box), you can use :

=LET(dash,FIND("~",B3,1),lowx,NUMBERVALUE(LEFT(B3,dash-1)),highx,NUMBERVALUE(MID(B3,dash+1,LEN(B3))),ymax,MAX(FILTER(N:N,(N:N>=lowx) *( N:N<=highx))),ymin,MIN(FILTER(N:N,(N:N>=lowx) *( N:N<=highx))),ymin&"~"&ymax)

I have assumed that your data range of values remains in column "N"

Hope this works for you.
Rob

 

[KRice]

In post #5, you mentioned…

I encountered an error for this formula. May I know what is the reason cause this happen?

The “This function isn’t valid“ message suggests that the FILTER function may not be available. Your profile shows two versions of Excel, with 2021 being the latest. Which version of Excel are you using when you see this error message?

 

[KRice]

See the green cells and let me know if they answer your most recent question:

MrExcel_20220803.xlsx
	N	O	P	Q	R	S
1	Data
2	666		Mode data <	650	628
3	702		Mode data >=	650	666
4	741		Min~Max		608~799
5	678		Min~Max of data < 650		608~646	608~646
6	673					min~max like (608~646)
7	707		Lower endpoint of range	600
8	737		Upper endpoint of range	700
9	729		Count of points in range		16
10	797		Count of points		26
11	731
12	799
13	729
14	710
15	655
16	608
17	628
18	658
19	643
20	646
21	666
22	628
23	636
24	641
25	677
26	626
27	646
Sheet9

Cell Formulas
Range	Formula
R2	R2	=MODE.SNGL(FILTER($N$2:$N$27,$N$2:$N$27<Q2))
R3	R3	=MODE.SNGL(FILTER($N$2:$N$27,$N$2:$N$27>=Q3))
R4	R4	=TEXT(MIN($N:$N),"0")&"~"&TEXT(MAX($N:$N),"0")
R5	R5	=TEXT(MIN(FILTER(N:N,N:N<650)),"0")&"~"&TEXT(MAX(FILTER(N:N,N:N<650)),"0")
S5	S5	=TEXT(AGGREGATE(15,6,(N2:N100)/((N2:N100<650)*(N2:N100<>"")),1),"0")&"~"&TEXT(AGGREGATE(14,6,(N2:N100)/((N2:N100<650)*(N2:N100<>"")),1),"0")
S6	S6	="min~max like (" & TEXT(AGGREGATE(15,6,(N2:N100)/((N2:N100<650)*(N2:N100<>"")),1),"0")&"~"&TEXT(AGGREGATE(14,6,(N2:N100)/((N2:N100<650)*(N2:N100<>"")),1),"0") &")"
R9	R9	=COUNTIFS(N:N,">="&Q7,N:N,"<="&Q8)
R10	R10	=COUNT(N:N)

 

[michellesssme]

In post #5, you mentioned…

The “This function isn’t valid“ message suggests that the FILTER function may not be available. Your profile shows two versions of Excel, with 2021 being the latest. Which version of Excel are you using when you see this error message?

Sorry about the inaccurate info, my Excel version is 2019.