Forum Discussion

ramesh98's avatar
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  
excel
  • HansVogelaar's avatar
    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's avatar
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

plus some records meet no one. If misprint you may correct the formula as needed.

  • ramesh98's avatar
    ramesh98
    Copper Contributor
    May 15, 2021

    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 

Resources