Forum Discussion
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")
In column D I list a branch of either CO, G&P, ST, LT, HDR
Each branch has different work hours. Instead of changing my formula based on what is in column D I would like to do a multiple if and statement.
So it would say if D3="G&P" and F3>6000, the result should be "Jrny" or if D3="LT" and F3>4000 the result should be "Jrny"
Column F shows total on the job hours earned. There are 4 periods and the period the person is at is based on completing 1/4 of the required work hours each period.
CO and GP branches have same hours, HDR has hours of 8000, 6000, 4000, 2000 and ST and LT are 4000, 3000, 2000, 1000
I tried this:
=
IF(AND(D3=”CO”,F3>6000),"Jrny",
IF(AND(D3=”CO”,F3>4500,)"4th",
IF(AND(D3=”CO”,F3>3000),"3rd",
IF(AND(D3=”CO”,F3>1500),"2nd",
IF(AND(D3=”CO”,F3<1500),"1st",
IF(AND(D3=”LT”,F3>4000),"Jrny",
IF(AND(D3=”LT”,F3>3000),"4th",
IF(AND(D3=”LT”,F3>2000),"3rd",
IF(AND(D3=”LT”,F3>1000),"2nd",
IF(AND(D3=”LT”,F3<1000),"1st")))))))))))
The above didn't work.
How would I write one formula that can be used and will base the answer on what is in Column D and in Column F?
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"))
9 Replies
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"))
- you are a lifesaver! Thank you. I am getting data for this sheet from an Access Database and every time I refresh the data it wanted to change these columns to all be the same so I figured if I just wrote one formula I wouldn't have that issue any more. I have one more column I need a similar formula for. This column calculates training hours needed based on the Branches listed above (Column D) and the resulting period of training that is now in Column G
For LT and ST I have the following formula:
=IF(G564="2nd",72,IF(G564="1st",72,IF(G564="3rd",144,IF(G564="4th",216,IF(G564="Jrny",288)))))
For CO, G&P and HDR I have:
=IF(G307="2nd",288,IF(G307="1st",144,IF(G307="3rd",432,IF(G307="4th",576,IF(G307="Jrny",576)))))
Now I need a formula like the one you gave me before. I thought I would be able to easily figure it out using what you gave me but its taking me a minute.
Thank you so much for your assistance. This is information I use almost daily and will save me a tremendous amount of work because before I was putting the info in both excel and Access just so I could print out a pretty report for the powers that be from excel. Now I only have to enter it into Access and refresh it in Excel.This one is less straightforward since the progression does not follow the same pattern
72 - 72 - 144 - 216 - 288
vs
288 - 144 - 432 - 576 - 576
Try this:
=LOOKUP(G3, {"2nd","1st","3rd","4th","Jrny"}, IF(OR(D3={"LT","ST"}), {72,72,144,216,288}, {288,144,432,576,576}))
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"))
