How to find mins in a column related to another column (excel MINIFS)

[AlexMunday]

The idea is to find the min of the value column related to the department column, but the min has to be different from 0.

then we want the second and third min

Here are the excel function used:

Small 1 = MINIFS($C:$C;$B:$B;$B2;$C:$C;"<>0")

Small 2 =MINIFS($C:$C;$B:$B;$B2;$C:$C;"<>0";$C:$C;CONCAT("<>";$D2))

Small 3 = =MINIFS($C:$C;$B:$B;$B2;$C:$C;"<>0";$C:$C;CONCAT("<>";$D2);$C:$C;CONCAT("<>";$E2))

I have put some data to illustrate:

Country	Departement	value	Min 1	Min 2	Min 3
CH	a	0,824831	0,491241	0,759035	0,824831
CH	b	0	0,562287	0,812621	0,866854
CH	c	0,02241	0,02241	0,09508	0,195738
CH	d	0,032302	0,032302	0,57665	0,748025
CH	e	0,411674	0,411674	0,710219	0,729571
CH	f	0,311402	0,131588	0,233024	0,311402
ES	a	0,759035	0,491241	0,759035	0,824831
ES	b	0,866854	0,562287	0,812621	0,866854
ES	c	0	0,02241	0,09508	0,195738
ES	d	0,760727	0,032302	0,57665	0,748025
ES	e	0,729571	0,411674	0,710219	0,729571
ES	f	0,233024	0,131588	0,233024	0,311402
BR	a	0	0,491241	0,759035	0,824831
BR	b	0,812621	0,562287	0,812621	0,866854
BR	c	0,09508	0,02241	0,09508	0,195738
BR	d	0,57665	0,032302	0,57665	0,748025
BR	e	0,796108	0,411674	0,710219	0,729571
BR	f	0,520732	0,131588	0,233024	0,311402
PR	a	0,491241	0,491241	0,759035	0,824831
PR	b	0,562287	0,562287	0,812621	0,866854
PR	c	0,195738	0,02241	0,09508	0,195738
PR	d	0,748025	0,032302	0,57665	0,748025
PR	e	0,710219	0,411674	0,710219	0,729571
PR	f	0,131588	0,131588	0,233024	0,311402

[AlB · Accepted solution]

Hi @AlexMunday 

Create a calculated column for the first min:

Min1 =
CALCULATE (
    MIN ( Table1[value ] );
    ALLEXCEPT ( Table1; Table1[Departement ] );
    Table1[value] <> 0
)

Then for min2 using the newly created column:

Min2 =
CALCULATE (
    MIN ( Table1[value ] );
    ALLEXCEPT ( Table1; Table1[Departement ] );
    Table1[value] <> 0;
     Table1[value] <> EARLIER( Table1[Min1])
)

and then for min3

Min3 =
CALCULATE (
    MIN ( Table1[value ] );
    ALLEXCEPT ( Table1; Table1[Departement ] );
    Table1[value] <> 0;
    Table1[value] <> EARLIER( Table1[Min1]);
    Table1[value] <> EARLIER( Table1[Min2]) 
)

[AlexMunday]

Thank you it's working perfectly