Feb 26 2024 09:55 AM
Hi, I am new to SharePoint and I am trying to create a formula based on the following scenario.
I have two Columns:
Column A - Field=Flight_x0023_ (the value entered here determines the item populated in Column B
Column B - Field= Airline
Column A- values 1 to 1999 = AX, values 2000 to 2999= BX , and values 3000 to 3999 =CX, or ""
Column B- Values that need to populate are, AX, BX, CX
I have tried different IF formulas and I receive an error stating to enter a valid condition, here is the last formula that i tried unsuccessfully. Any help would be appreciated.
=IF([$Flight_x0023_]<=1999,"AX",IF(AND([$Flight_x0023_]>2000,[$Flight_x0023_]<=2999),"BX",IF([$Flight_x0023_]>3000,"CX", "")))
Feb 27 2024 04:33 AM
@RenPNW If you are trying to create a calculated column for Column B, use formula like:
=IF([Flight Column]<=1999,"AX",IF(AND([Flight Column]>2000,[Flight Column]<=2999),"BX",IF([Flight Column]>3000,"CX","")))
Where [Flight Column] is the display name of your column and NOT internal name.
Note:
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Feb 27 2024 12:49 PM
@ganeshsanap Thank you for your feedback! I made the corrections as you instructed and I it did not seem to work. To further complicate the issue, the column that houses the flight numbers is formatted as text so I am using VALUE to convert to number. I have been able to get this code to work:
=IF(VALUE([Flight #])<2000,"AS"," ")
but anytime I try to add the IF(AND and the rest of the code it does not like it and throws a technical error.
What could I be doing wrong?
Thanks again!
Feb 27 2024 09:59 PM
Solution@RenPNW Try using formula in this format:
=IF(ISBLANK([Flight #]),"",IF(VALUE([Flight #])>=3000,"CX",IF(VALUE([Flight #])>=2000,"BX",IF(VALUE([Flight #])>=1,"AX",""))))
OR
=IF(ISBLANK([Flight #]),"",IF(VALUE([Flight #])<=1999,"AX",IF(AND(VALUE([Flight #])>2000,VALUE([Flight #])<=2999),"BX",IF(VALUE([Flight #])>=3000,"CX",""))))
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Mar 03 2024 07:54 AM
@ganeshsanap - Thank you for your help on this, the first formula on your reply did the trick! Thank you very much for your help!
Ren
Feb 27 2024 09:59 PM
Solution@RenPNW Try using formula in this format:
=IF(ISBLANK([Flight #]),"",IF(VALUE([Flight #])>=3000,"CX",IF(VALUE([Flight #])>=2000,"BX",IF(VALUE([Flight #])>=1,"AX",""))))
OR
=IF(ISBLANK([Flight #]),"",IF(VALUE([Flight #])<=1999,"AX",IF(AND(VALUE([Flight #])>2000,VALUE([Flight #])<=2999),"BX",IF(VALUE([Flight #])>=3000,"CX",""))))
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.