I’m just confused here and would like some more insight into what’s happening and if it’s on my end or not.
I have a Google Sheet with a bunch of quiz data, and I need to calculate the percent error over all respondents. I have the correct answer in column A, the user’s input in the respective row in column B, and error in column C as A-B. $\left\{ A, B, C \in \mathbb{Z} \right\}$ <- LaTeX (A, B, and C all contain integers).
Here’s my data:
| math input | math correctAnswer | math error |
|---|---|---|
| 93 | 93 | 0 |
| 84 | 84 | 0 |
| 110 | 111 | -1 |
| 163 | 163 | 0 |
| 52 | 52 | 0 |
| 85 | 85 | 0 |
| 103 | 103 | 0 |
| 102 | 112 | -10 |
| 98 | 98 | 0 |
| 136 | 136 | 0 |
| 70 | 70 | 0 |
| 119 | 119 | 0 |
| 63 | 63 | 0 |
| 174 | 174 | 0 |
| 82 | 82 | 0 |
| 109 | 109 | 0 |
| 92 | 92 | 0 |
| 34 | 34 | 0 |
| 95 | 95 | 0 |
| 126 | 126 | 0 |
| 78 | 78 | 0 |
| 67 | 87 | -20 |
| 164 | 164 | 0 |
| 116 | 116 | 0 |
| 100 | 100 | 0 |
| 19 | 122 | -103 |
| 118 | 118 | 0 |
| 92 | 91 | 1 |
| 103 | 103 | 0 |
| 84 | 84 | 0 |
| 98 | 98 | 0 |
| 114 | 114 | 0 |
| 104 | 114 | -10 |
| 53 | 53 | 0 |
| 121 | 121 | 0 |
| 136 | 136 | 0 |
| 140 | 140 | 0 |
| 76 | 76 | 0 |
| 150 | 150 | 0 |
| 80 | 80 | 0 |
| 125 | 125 | 0 |
| 129 | 129 | 0 |
| 109 | 109 | 0 |
| 112 | 112 | 0 |
| 174 | 172 | 2 |
| 161 | 161 | 0 |
| 147 | 147 | 0 |
| 63 | 63 | 0 |
| 93 | 93 | 0 |
| 113 | 113 | 0 |
| 138 | 138 | 0 |
| 168 | 168 | 0 |
| 69 | 69 | 0 |
| 77 | 77 | 0 |
| 105 | 105 | 0 |
| 41 | 41 | 0 |
| 79 | 69 | 10 |
| 45 | 45 | 0 |
| 33 | 33 | 0 |
| 121 | 156 | -35 |
| 116 | 116 | 0 |
| 169 | 169 | 0 |
| 111 | 111 | 0 |
| 137 | 138 | -1 |
| 131 | 131 | 0 |
| 121 | 121 | 0 |
| 117 | 117 | 0 |
| 89 | 89 | 0 |
| 168 | 168 | 0 |
| 129 | 129 | 0 |
| 134 | 134 | 0 |
| 182 | 182 | 0 |
| 139 | 139 | 0 |
| 100 | 100 | 0 |
| 153 | 153 | 0 |
| 54 | 54 | 0 |
| 126 | 126 | 0 |
| 49 | 49 | 0 |
| 84 | 84 | 0 |
| 93 | 93 | 0 |
| 56 | 56 | 0 |
| 116 | 116 | 0 |
| 78 | 78 | 0 |
| 96 | 96 | 0 |
| 118 | 118 | 0 |
| 138 | 138 | 0 |
| 62 | 62 | 0 |
| 53 | 53 | 0 |
| 79 | 79 | 0 |
| 33 | 33 | 0 |
| 32 | 32 | 0 |
| 111 | 111 | 0 |
| 194 | 194 | 0 |
| 114 | 124 | -10 |
| 169 | 169 | 0 |
| 118 | 118 | 0 |
| 126 | 126 | 0 |
| 80 | 80 | 0 |
| 133 | 133 | 0 |
| 50 | 54 | -4 |
| 156 | 176 | -20 |
| 154 | 164 | -10 |
| 34 | 34 | 0 |
| 50 | 50 | 0 |
| 92 | 92 | 0 |
| 109 | 109 | 0 |
| 143 | 143 | 0 |
| 107 | 107 | 0 |
| 113 | 113 | 0 |
| 120 | 139 | -19 |
| 84 | 84 | 0 |
| 92 | 92 | 0 |
| 41 | 41 | 0 |
| 77 | 77 | 0 |
| 59 | 59 | 0 |
| 108 | 108 | 0 |
| 115 | 115 | 0 |
| 122 | 122 | 0 |
| 128 | 134 | -6 |
| 58 | 58 | 0 |
| 53 | 53 | 0 |
| 95 | 95 | 0 |
| 152 | 152 | 0 |
| 166 | 166 | 0 |
| 99 | 99 | 0 |
| 129 | 129 | 0 |
| 156 | 156 | 0 |
| 166 | 166 | 0 |
| 110 | 110 | 0 |
| 70 | 70 | 0 |
| 110 | 149 | -39 |
| 127 | 127 | 0 |
| 121 | 121 | 0 |
| 100 | 100 | 0 |
| 120 | 120 | 0 |
| 73 | 73 | 0 |
| 50 | 50 | 0 |
| 154 | 154 | 0 |
| 72 | 72 | 0 |
| 116 | 116 | 0 |
| 107 | 107 | 0 |
| 158 | 155 | 3 |
| 125 | 125 | 0 |
| 70 | 70 | 0 |
| 46 | 46 | 0 |
| 103 | 146 | -43 |
| 153 | 153 | 0 |
| 46 | 46 | 0 |
| 118 | 118 | 0 |
| 112 | 112 | 0 |
| 108 | 108 | 0 |
| 118 | 118 | 0 |
| 76 | 120 | -44 |
| 124 | 124 | 0 |
| 90 | 90 | 0 |
| 123 | 123 | 0 |
| 149 | 149 | 0 |
| 102 | 102 | 0 |
| 160 | 160 | 0 |
| 127 | 127 | 0 |
| 82 | 82 | 0 |
| 104 | 104 | 0 |
| 134 | 134 | 0 |
I originally just used COUNTIF(C2:C10000, "=0")/COUNTA(C2:C10000) and made a formula for it, which worked fine, but someone suggested I use PERCENTIF.
They’re the same idea, at least documentation-wise, but COUNTIF(C2:C10000, "=0")/COUNTA(C2:C10000) outputs 0.8773006135, which is correct, whereas PERCENTIF(C2:C10000, "=0") outpus 14%, which is not correct nor is the inverse of 0.8773006135.
Is this just really bad floating point precision error? Are these functions actually different?
Blank cells beyond your data range are by default evaluated to zero → so the 14%
either hardcode the range as
=percentif(C2:C164,0)
OR
=percentif(filter(C2:C,C2:C<>""),0)
& you’ll see the 87.73%