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")))))))

Patrick2788's avatar
Patrick2788
Silver Contributor
May 15, 2021

I realize that not all rank 5s or rank 9s are the same, for example. I thought I'd take a different approach to the logic offered and go based on positioning.

=IFNA(VLOOKUP(RANK.AVG(B3,B3:K3),{1,"Long term uptrend intact";2,"Chance of initiation of uptrend";3,"Uptrend initiated";6,"Check support at 20 DEMA";8,"Downtrend initiated";9,"Chance of initiation of downtrend";10,"Long term downtrend intact"},2,0),"Other")

  • ramesh98's avatar
    ramesh98
    Copper Contributor
    May 16, 2021
    Working nicely thanks. Will check by adding conditions

Resources