What am I missing in the formula?
Here is the scenario:
Column 1 is the harvey ball symbol status - empty, quarter, three quarter, full
The goal is that each time one of the other columns is filled in with any date or a checkbox tick that the status changes.
Here is my current formula:
=IF(NOT(ISBLANK([Date Received]@row)), "Quarter", IF([CA Review Complete]@row = 1, "Half", IF([CA Approval Complete]@row = 1, "Three Quarter", IF(NOT(ISBLANK([Date Completed]@row)), "Full", IF(ISBLANK([Date Received]@row), "Empty")))))
The problem: it will only do one action at a time. It does not build. So if a date is filled in, the status changes to quarter, but if then the next column is ticked it does not change to half. If i delete the date then it will go to half.
Any help is greatly appreciated.
Best Answer
-
Do it backwards start with the final "full" as your first if statement then back to Three Quarter then back to half then back to Quarter. The order of an If statement determines the next step and end with your Empty statement as it should only be empty if none of the other criteria are met.
Answers
-
Do it backwards start with the final "full" as your first if statement then back to Three Quarter then back to half then back to Quarter. The order of an If statement determines the next step and end with your Empty statement as it should only be empty if none of the other criteria are met.
-
Yes! this worked! I have a follow up question that maybe you can help me out with. I have this column linked to a metrics sheet to use in a dashboard.
Currently I have the top row that shows the total and the formula there is: =COUNTIF({Page Status}, "Quarter")
The next row is counting how many are in the next status. What I would like to do is now subtract from that first row, so by the time it goes to a row labeled "Full" or "Complete" the other rows are showing 0.
-
Hi @Nana250
Would you mind showing a screen capture of this metric sheet and clarify what exactly you want it to do?
Formulas are dynamic, which means that as your values change the number will change. This means that if you had a cell that was "Quarter" but then it changes, your COUNTIF statement will first say 1, then it will shift to 0 when the cell is changed to another value. Is this what you wanted?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!