Forum Discussion
Solved
Range of values in one column determine value of second column
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 popula...
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.
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:
- Sometimes comma( , ) does not work in formula (it is based on language or regional settings of your SharePoint site). So in that case use semicolon( ; ) instead of comma( , ).
- Use correct display name of your SharePoint columns in above formula.
- Wrap column names inside [] if your column name has space in it. For example: [My Column Name].
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.
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!
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.
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
