Forum Discussion
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")...
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"))
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}))
- Finally got the formulas all fixed to do what I need. Saved the document. Re-opened it, hit refresh data and then it reverted all the formulas to the previous ones I had input. I don't understand why it does that after saving, closing and re-opening
