HELP - Excel Question

[Jmaddog]

Please help with excel formula question:
Working on an efficiency ranking spreadsheet including the following:
There are six categories. Each has a ranking 1-5, 5 being the best. Those categories are all weighted and added together for the total ranking, which is your outcome. If there is no data in categories 1 and 2 how do you get the total ranking to skip those and only count 3, 4, and 5 in the total ranking? We are looking for a formula so we don’t have to look at each person individually.

 

[Rabbie]

You would be more likely to get a reply if you posted your query in the Excel thread rather than the Access General one

 

[DanWall]

An excel question? Cool.

assume A1 to A5 is rank 1 to 5. A6 is the TOTAL

in the A6 cell, I would put something like (depending on your totalling formula or course)

=sum(a1:a5)/count(a1:a5)

Is this close to what you need?

 

[Jmaddog]

This is close.

How do we calculate the ranking if we have "0" in one of the cells, but we do not want to include the "0" as part of the ranking. Currently the formula calculates the "0" in the ranking.

 

[DanWall]

OK. Use the Countif function.

=sum(a1:a5)/countif(a1:a5,">0")