Forum Discussion
Need formula based on conditions for lower or higher column values
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")))))))
ramesh98 If you are open to a more graphic representation of the trends, you might want to consider a variety of Sparklines as demonstrated in the attached sheet. In this case, I chose the Winn/Loss type with markers for highest (green) and lowest (red). The "Trend" column in M fairly accurately represents the textual trend descriptions by looking at the placement of the different markers.
Just another way to look at things.
