Apr 12 2022 09:35 AM - edited Apr 12 2022 12:02 PM
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?
Apr 12 2022 12:25 PM - edited Apr 12 2022 12:31 PM
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"))
Apr 12 2022 12:31 PM
SolutionIf 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"))
Apr 12 2022 02:35 PM
Apr 12 2022 02:50 PM
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}))
Apr 12 2022 03:34 PM
Apr 12 2022 04:17 PM
Apr 12 2022 04:34 PM
Apr 12 2022 04:52 PM
Apr 12 2022 12:31 PM
SolutionIf 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"))