Order of Operations -- PEMDAS syntax

Dan B.

Hello, I am currently designing a Quiz, but I am having an issue with my syntax and I was hoping someone could explain what I'm doing wrong. Basically, I have a test that is worth 208 total points.

Let's say that there are 84 questions and each question is worth 1, 2, 4, or 6 points.

Here is where the twist comes in. Sometimes the answer to a question will be "N/A". So for example, if there is one N/A answer on a question that is worth 2 points, I need the the total to shift from 208 to 206 total points.

What I currently have done is have N/A be worth the inverse of a full credit. So if a question was worth 2 points if they are correct, I would have the N/A value be -2. Then I have 2 helper columns to run a SUMIF. Helper column 1 sums up all the number values above 0, and then Helper column 2 sums up all the number less than 0 and then multiplies it by -1 to get a positive value.

Then my result column basically: Helper Column 1 / (208 - Helper Column 2). This works, but I was wondering if there was a way to rewrite it so I didn't have to use 2 additional helper columns

=(SUMIF([Data Range], > 0) / (208 - ((SUMIF([Data Range] < 0) *-1))

However, I keep getting a syntax error. Am I missing something or is this a smartsheet limitation?

KPH

Hi @Dan B.

Is this the formula copied directly from your cell:

=(SUMIF([Data Range], > 0) / (208 - ((SUMIF([Data Range] < 0) *-1))

If so, I think your syntax error is caused by this part: [Data Range]

If Data Range is the column name, to SUM the entire column, you would use [Data Range]:[Data Range]

If it is a cross sheet reference to a column in another sheet it would be {Data Range}

Dan B.

Sorry for the delay on it, but it's [Data Range]@row:[Data Range]@row

The way my sheet is set up is that each column is an "answer" and the column next to it is a "point value". The answers are text, the point values are number values.