Forum Discussion
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
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")))))))
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
plus some records meet no one. If misprint you may correct the formula as needed.
Sir, sorry I have attached data again. There was small error in conditions at D15/D17. Also kindly help resolve in attached sheet itself SergeiBaklan