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")))))))
The conditions in D13 and D15 are the same, and the conditions in D14 and D16 are also the same.
data corrected, pl check again thanksHansVogelaar
In L3:
=IFS(B3>MAX(C3:K3),$H$11,B3<MIN(C3:K3),$H$12,AND(B3>C3,B3<MIN(D3:K3)),$H$13,AND(B3<C3,B3>MAX(D3:K3)),$H$14,AND(B3>MAX(C3:D3),B3<MIN(E3:K3)),$H$15,AND(B3<MAX(C3:D3),B3>MIN(E3:K3)),$H$16,AND(B3>MAX(C3:F3),B3<MIN(G3:K3)),$H$17,TRUE,"Other")
Fill down.
Sir thanks but still showing name error. Kindly check attached sheet HansVogelaar
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")))))))