Order of Operations -- PEMDAS syntax
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?
Answers
-
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}
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 286 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!