Excel Formula 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.

 

[DanWall]

Oops, multipost

Assume a1 to a5 are your ranks. A6 is the total.

A6 cell's formula would be:
=sum(a1:a5)/countif(a1:a5,">0")

 

[Jmaddog]

How can we incorporate this formula if we already have a formula in the total cell (a6)?

 

[DanWall]

It depends on the formula.

May I see it?

 

[Jmaddog]

Here it is, it is going to be confusing, it is a weighted average:

=(H7*(0.2)+J7*(0.05)+L7*(0.1)+N7*(0.25)+P7*(0.15)+R7*(0.1)+T7*(0.15))

 

[DanWall]

It's tricky, as there is probably

I don't understand if you need to divide by the count or not, but you would simply append the countif statement to the end....
=(H7*(0.2)+J7*(0.05)+L7*(0.1)+N7*(0.25)+P7*(0.15)+ R7*(0.1)+T7*(0.15)) / countif(H7:T7,">0")

It should only count the numeric values.
I'm assuming that the intermediate cells are labels? ie: G7=Label1 I7=Label2 K7=Label3 etc?

 

[Jmaddog]

What if we have DIV/0! inplace of "0" how would we write this in the formula?

 

[DanWall]

would it be difficult to amend the cells with the DIV/0! error?

I usually put: =if(iserror( your formula ), "", your formula)

This will make the cell return a blank (equal to 0) rather than an error.

 

[DanWall]

as an example:

in the H7 cell: =if(iserror(0/0),"",0/0)

basically, if Zero divided by Zero produces an error (it WILL produce an error), then make the cell read BLANK. Else, the answer is Zero divided by Zero.

 

[DanWall]

Did this do the trick?

 

[Jmaddog]

Thank you so much for all your help. It is working.

This is going to save so much time.

Thanks again.

 

[DanWall]

No worries