Forum Discussion

nnvjac's avatar
nnvjac
Copper Contributor
Apr 12, 2022
Solved

IF AND statement help

I have current formulas of    =IFS(F3>6000,"Jrny",F3>4500,"4th",F3>3000,"3rd",F3>1500,"2nd",F3<1500,"1st")   =IFS(F527>4000,"Jrny",F527>3000,"4th",F527>2000,"3rd",F527>1000,"2nd",F527<1000,"1st")...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 12, 2022

    nnvjac 

    If you have Microsoft365 or Office 2021:

    =LET(x, IFS(D3="HDR", 2000, OR(D3={"CO","G&P"}), 1500, OR(D3={"ST","LT"}), 1000), IFS(F3>4*x, "Jrny", F3>3*x, "4th", F3>2*x, "3rd", F3>x, "2nd", F3<=x, "1st"))

HansVogelaar's avatar
HansVogelaar
MVP
Apr 12, 2022

nnvjac 

For example

 

=IFS(D3="HDR", IFS(F3>8000,"Jrny",F3>6000,"4th",F3>4000,"3rd",F3>2000,"2nd",F3<=2000,"1st"), OR(D3={"CO","G&P"}), IFS(F3>6000,"Jrny",F3>4500,"4th",F3>3000,"3rd",F3>1500,"2nd",F3<=1500,"1st"), OR(D3={"ST","LT"}), IFS(F3>4000,"Jrny",F3>3000,"4th",F3>2000,"3rd",F3>1000,"2nd",F3<=1000,"1st"))

Resources