Nesting IF AND OR all together

[b19upj]

HI,

I have a question card with 5 questions 1,2,3,4,5 going down column A1, A2, A3, A4, A5.
I would like to work out the percentage of the score where the answers can be from 1-5 in column B next to its respective question number. BUT the questions can also be answered 0 if not applicable and i don't want that answer to be included in the percentage calculation.
So if i get the following results for each question 5,5,5,5,5 it will = 100%
but if i get 0,5,0,5,5 it should also = 100% (as the 0 should not be counted in the calculation).

Hope that make sense

 

[Fishboy]

Try this:

=SUM(B1:B5)/(COUNTIF(B1:B5,">"&0)*5)

Set the format of this cell to be percentage with zero decimals.

 

[b19upj]

Try this:

=SUM(B1:B5)/(COUNTIF(B1:B5,">"&0)*5)

Set the format of this cell to be percentage with zero decimals.

I get a #DIV/0 error with tthe above formulae.

Any other ideas?

 

[b19upj]

My mistake, i had wired it into my spreadsheet wrongly.
This formula works perfectly.

Many thanks!!!!

 

[Fishboy]

My mistake, i had wired it into my spreadsheet wrongly.
This formula works perfectly.

Many thanks!!!!

Happy to help