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")))))))
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")))))))
- Working fine. Thanks a lot sir