IF Formula assigning scores
Hello,
I have a formula (below) that assigns scores depending on a criteria and whether or not the checkbox is checked. I.e. If the Governor's Dashboard is checked, it will assign 4 points to the total project score. I have one item that I no longer want to be a checkbox (bolded below).
I would like "Do you have required funding? (3,0) NA=Check" To be a drop down where if "Yes" or "NA" is selected, it applies a score of 3. How would I implement that into this formula?
=IF([Governor's Dashboard (4,0)]@row, 4) + IF([Dept WIG (3,0)]@row, 3) + IF([Office WIG (2,0)]@row, 2) + IF([Division WIG (1,0)]@row, 1) + IF([CStat (1,0)]@row, 1) + IF([Savings over $1 Million (3,0)]@row, 3) + IF([Savings $500k -$999k (2,0)]@row, 2) + IF([Savings of over $100K-$499 (1,0)]@row, 1) + IF([CDHS Strategic Plan (2,0)]@row, 2) + IF([Legislative or Regulatory Requirement - Statu(4,0)]@row, 4) + IF([Legislative or Regulatory Requirement -Fed AU(3,0)]@row, 3) + IF([Legislative or Regulatory Requirement OSA(2,0)]@row, 2) + IF([Legislative or Regulatory Requirement- Int A (1,0)]@row, 1) + IF([Client Safety (4,0)]@row, 4) + IF([Broad and Direct Stakeholder Impact (2,0)]@row, 2) + IF([Improve Security IT-Risk etc (4,0)]@row, 4) + IF([Better Outcomes for Our Clients (4,0)]@row, 4) + IF([Do you have required funding? (3,0) (NA=check)]@row, 3) + IF([How many stakeholders impacted? or ppl trained]@row < 99.9, 0, IF([How many stakeholders impacted? or ppl trained]@row < 499.9, 1, IF([How many stakeholders impacted? or ppl trained]@row < 999.9, 2, 3)))
Best Answer
-
Assuming your options are "Yes, NO, NA" then your IF() statement could be...
IF( [Do you have required funding? (3,0) NA=Check]@row <> "NO", 3)
Otherwise, you can include OR() in your IF() statement...
IF( OR([Do you have required funding? (3,0) NA=Check]@row = "Yes", [Do you have required funding? (3,0) NA=Check]@row = "NA" ), 3)
You can also use a nested IF() statement...
IF( [Do you have required funding? (3,0) NA=Check]@row = "Yes",3, IF([Do you have required funding? (3,0) NA=Check]@row@="NA", 3))
Additional documentation on Smartsheet functions can be found here, https://help.smartsheet.com/functions
Answers
-
Assuming your options are "Yes, NO, NA" then your IF() statement could be...
IF( [Do you have required funding? (3,0) NA=Check]@row <> "NO", 3)
Otherwise, you can include OR() in your IF() statement...
IF( OR([Do you have required funding? (3,0) NA=Check]@row = "Yes", [Do you have required funding? (3,0) NA=Check]@row = "NA" ), 3)
You can also use a nested IF() statement...
IF( [Do you have required funding? (3,0) NA=Check]@row = "Yes",3, IF([Do you have required funding? (3,0) NA=Check]@row@="NA", 3))
Additional documentation on Smartsheet functions can be found here, https://help.smartsheet.com/functions
-
@Toufong Vang Thank you so much! I ended up using the IF/OR statement so it wouldn't count if it was blank! This worked wonderfully.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!