Hi All,
looking for a bit of help, not sure if this is possible but here goes!
I have table for this example i have just added in 1 question. Yes = 100% no = 0% N/A - excluded but i still wanted it counted against avg score.
So for example
Mr Smith has been scored 4 times on question 1
1st = No
2nd = Yes
3rd = N/A
4th = Yes
I would like the pivot to show as 66.75% avg as only 1 wrong and the N/A while still counted isn't measured against. I could make the n/a = 100% but this would avg out at 75% which im not looking for so im a bit stuck.
looking for a bit of help, not sure if this is possible but here goes!
I have table for this example i have just added in 1 question. Yes = 100% no = 0% N/A - excluded but i still wanted it counted against avg score.
So for example
Mr Smith has been scored 4 times on question 1
1st = No
2nd = Yes
3rd = N/A
4th = Yes
I would like the pivot to show as 66.75% avg as only 1 wrong and the N/A while still counted isn't measured against. I could make the n/a = 100% but this would avg out at 75% which im not looking for so im a bit stuck.
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Name | Question1 | Q1SCORE | ||
| 2 | Mr Smith | No | 0% | ||
| 3 | Mr Smith | Yes | 100% | ||
| 4 | Mr Smith | N/A | |||
| 5 | Mr Smith | Yes | 100% | ||
| 6 | |||||
| 7 | What I would like it to read when avg on pivot | ||||
| 8 | |||||
| 9 | Question1 | ||||
| 10 | Mr Smith | 66.75% | |||
| 11 | |||||
| 12 | Row Labels | Average of Q1SCORE | |||
| 13 | Mr Smith | #DIV/0! | |||
| 14 | Grand Total | #DIV/0! | |||
Sheet1 | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C5 | C2 | =IFS(B2="Yes","100%",B2="No","0%",B2="N/A","") |
