funkySweet
New Member
- Joined
- Jun 15, 2016
- Messages
- 1
I am trying to calculate score that has 5 sections( minimum of 3 questions per sections). Among these some can be N/A. I need to calculate in such a way that the first 4 parts should be (Total Yes answers) / (total Yes+NO questions) * 10 and the 5th part should be multiplied by 60. Finally I need to sum all 5 parts to get a final score .
My Solution : (first 4 parts)
=IFERROR((COUNTIF(D70:D72,"Yes")/SUM(COUNTIF(D70:D72,"Yes"),(COUNTIF(D70:D72,"No"))))*10,"N/A")
The above formula continues for other 3 sections with different range values
5th part : =IFERROR((COUNTIF(D70:D72,"Yes")/SUM(COUNTIF(D70:D72,"Yes"),(COUNTIF(D70:D72,"No"))))*60,"N/A")
Final Score : =AGGREGATE(9,6,(G30,G42,G52,G64,G73))/100
I tested my formula having one section has N/A and other has YES. This gives me a result of 90% rather than 100%
My question is what if one of the parts is completely n/a. then how I should ignore the n/a section and still get 100%
My Solution : (first 4 parts)
=IFERROR((COUNTIF(D70:D72,"Yes")/SUM(COUNTIF(D70:D72,"Yes"),(COUNTIF(D70:D72,"No"))))*10,"N/A")
The above formula continues for other 3 sections with different range values
5th part : =IFERROR((COUNTIF(D70:D72,"Yes")/SUM(COUNTIF(D70:D72,"Yes"),(COUNTIF(D70:D72,"No"))))*60,"N/A")
Final Score : =AGGREGATE(9,6,(G30,G42,G52,G64,G73))/100
I tested my formula having one section has N/A and other has YES. This gives me a result of 90% rather than 100%
My question is what if one of the parts is completely n/a. then how I should ignore the n/a section and still get 100%
