IF Formula assigning scores

Maria Watters

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)))

Toufong Vang

@Maria Watters

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

@Maria Watters

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

Maria Watters

@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.