NEW TO SMARTSHEETS!! Needing advice on creating a formula for N/A answers not to count
Two different equations are being used.
Equation 1: =IFERROR((SUM([RN Section 1 Score]@row, [RN Section 2 Score]@row, [RN Section 3 Score]@row, [RN Section 4 Score]@row)) / SUM(IF([RN Section 1 Score]@row = "", 0, 0.3), IF([RN Section 2 Score]@row = "", 0, 0.3), IF([RN Section 3 Score]@row = "", 0, 0.2), IF([RN Section 4 Score]@row = "", 0, 0.2)), "%")
Equation 2: =SUM([MD Section 1 Score]@row, [MD Section 2 Score]@row, [MD Section 3 Score]@row, [MD Section 4 Score]@row)
Equation 1 is used to obtain the average of a weighted score for 4 section. Each section is weighted differently.
Equation 2 is used to obtain the average of 4 sections of equal weights for the 4 sections of a different set of data.
Section 4 in each set of data may or may not be used depending on the type of information collected. If not relevant, the auto populated answer is "N/A". I need a formula that will populate an average, excluding section 4 if it is not used, and any N/A's throughout the data.
I have tried this formula: =COUNTIFS([MD Section 1 Score]@row, [MD Section 2 Score]@row, [MD Section 3 Score]@row, [MD Section 4 Score]@row), "Met") / COUNTIFS([MD Section 1 Score]@row, [MD Section 2 Score]@row, [MD Section 3 Score]@row, [MD Section 4 Score]@row), NOT([MD Section 1 Score]@row, [MD Section 2 Score]@row, [MD Section 3 Score]@row, [MD Section 4 Score]@row), = "N/A")), however it returns a syntax error.
Answers

Hey @S. Roach
The original formula does not have the syntax smartsheet is expecting. Correcting the syntax however will not correct the formula logic.
I will make an assumption that your columns are either contiguous or you don't have other columns in between that have a response of 'Met' or 'N/A'. If my assumption is incorrect please let me know.
=COUNTIFS([MD Section 1 Score]@row:[MD Section 4 Score]@row), "Met") / COUNTIFS([MD Section 1 Score]@row:[MD Section 4 Score]@row), <>"N/A")
Will this formula work for you?
Kelly

Hi Kelly!
Thank you for the response. Each section has a set of questions (columns) that will be Met, Not Met, N/A. The auto fill for these columns is N/A. And then the total for each section is weight at 25%. Depending on the audit being done, section 4 may be completely N/A, but all other sections are either Met, or N/A. When section 4 is N/A, these audits are coming back 80% instead of 100%
Section 1= column headers "1.1 MD, 1.2 MD, 1.3 MD"
Section 2= column headers "2.1 MD>2.18 MD" (each column is the respective number 2.12.18
Section 3= column headers "3.1 MD 3.3 MD"
Section 4= column headers "4.1 MD4.3 MD"
Help Article Resources
Categories
Check out the Formula Handbook template!