Need IF statement help
I am trying to build a formula using Duration to auto-select one of four statuses. Below is the formula I'm trying to build:
If Duration is less than/equal to 30 days, then = "Transition" OR Duration is greater than 30 days and less than/equal to 60 days, then = "Reshaping", OR Duration is less than/equal to 90 days, then = "Alignment", OR Duration is greater than 90 days, then "Implementation"
I tried to build it using IF(Or statements but it is coming back as Unparseable. Any thoughts on how to build this expression efficiently?
Answers
-
Hi Alisa
Try this. As I thought about your problem, I substituted the word IF in my thinking, in place of your OR, to better clarify the different fragments of your equation.
=IF(Duration@row<=30,"Transition", IF(AND(Duration@row>=30, Duration@row<=60),"Reshaping",IF(Duration@row, <=90, "Alignment","Implementation")))
-
Thanks! This is giving me mixed results. It either results in "Transition" or Incorrect Argument. It is interesting that in one line the duration is 129d but it is resulting in Transition. Other lines where the duration is more than 30d, it is resulting in Incorrect Argument. This is still closer than I could get!
-
I take that back, I repasted it and it results in "Transition" for every line. So it isn't making it past the first IF statement.
Thoughts?
-
Oops try this.
=IF(Duration@row<30,"Transition", IF(AND(Duration@row>=30, Duration@row<=60),"Reshaping",IF(Duration@row, <=90, "Alignment","Implementation")))
I got rid of the first equal to.
-
Thanks! Closer still! It works where duration is 30 days or less, but still gives me an incorrect argument on lines where duration is more than 30 days.
-
I did some additional testing and it looks like it is ok with the first and second IF's and returns the correct result for less than 30 days and for between 30 & 60 days. It doesn't like it when the duration is greater than 60 days or greater than 90 days. In those cases, it gives me an Incorrect Argument result.
-
I changed the last half of the statement so that the 3rd IF is between 60 and 90 days. Now it gives me the correct results for the first and second IF but gives me an Invalid Data Type on all other lines. Below is the revised formula
=IF(Duration@row < 30, "Transition", IF(AND(Duration@row >= 30, Duration@row <= 60), "Reshaping", IF(AND(Duration@row, >60, Duration@row <= 90), "Alignment", "Implementation")))
Thoughts?
-
Try
=IF(Duration@row<31,"Transition",IF(AND(Duration@row>30,Duration@row<61),"Reshaping",IF(AND(Duration@row>60,Duration@row<91),"Alignment","Implementation")))
Does this work?
Good luck
Debbie
-
Wow Debbie! You are a miracle worker! That fixed it! Thanks!
Thanks to KDM as well! I appreciate this community so much!!!!
-
You're welcome, to be honest, I started writing the formula before any other answers had been submitted, but then I took a zoom call and was with a client for 50 mins! I finished writing your formula and posted! I didn't see any of the thread at all!
Glad it worked! :D
PS just looking at some earlier formulas in the thread, there were some additional comma's which would have thrown up errors...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!