Forum Discussion

ramesh98

Copper Contributor

May 15, 2021

Solved

Need formula based on conditions for lower or higher column values

I need help for getting output in Remarks column based on conditions data. The data is given in attached excel file

data.xlsx11 KB

excel

HansVogelaar
May 15, 2021
ramesh98 Apparently you have an older version of Excel that doesn't have the IFS function.

This version will work in all versions:

=IF(B3>MAX(C3:K3),$H$11,IF(B3<MIN(C3:K3),$H$12,IF(AND(B3>C3,B3<MIN(D3:K3)),$H$13,IF(AND(B3<C3,B3>MAX(D3:K3)),$H$14,IF(AND(B3>MAX(C3:D3),B3<MIN(E3:K3)),$H$15,IF(AND(B3<MAX(C3:D3),B3>MIN(E3:K3)),$H$16,IF(AND(B3>MAX(C3:F3),B3<MIN(G3:K3)),$H$17,"Other")))))))

SergeiBaklan

Diamond Contributor

May 15, 2021

ramesh98

As variant

=IFS(
SUMPRODUCT(--(B3>C3:K3))=9,$H$11,
SUMPRODUCT(--(B3<C3:K3))=9,$H$12,
(B3>C3)*(SUMPRODUCT(--(B3<D3:K3)))=8,$H$13,
(B3<C3)*(SUMPRODUCT(--(B3>D3:K3))=8),$H$14,
(B3>C3)*(SUMPRODUCT(--(B3<D3:K3))=8),$H$15,
(B3<C3)*(SUMPRODUCT(--(B3>D3:K3))=8),$H$16,
(SUMPRODUCT(--(B3>C3:F3))=4)*
(SUMPRODUCT(--(B3<G3:K3))=5),$H$17,
TRUE, "nothing")

In your sample different conditions use same logic