Excel 2007 : RANKING TABLE #DIV/0! and #N/A

**Merv** · 12-21-2009, 06:54 AM

Hi i have a seriuos of sheets which rate workers performance but there two problems which i cant fix.

The first is that originaly i had the formula

=((C6+D6+E6+F6+G6)/4)/100

which was used for the not reay times average with the division amount changed by the day they worked. I then changed that to

=((C5+D5+E5+F5+G5)/COUNTIF(C5:G5,"<>0 "))/100

so that it would count these day automatically for me. but the problem then was that my tables for ranking them now does not work. and comes up with a #DIV0! error.

The second problem that i have is that some of the tables are displaying n/a when rating agetns on hang ups and long calls when they have the same amount.

is there a way round both these problems.

**arthurbr** · 12-21-2009, 09:04 AM

Hi,,
you can replace your average formula with

=average(if(you_range<>0,your_range,false)) and enter as an array formula with Ctrl+Shift+Enter

As for the second part, is is based on values in col T which do not seem coherent

the COUNTIF part has ranges like $q1:$q4 but the range in the following cell is like $q1:q$5.
Could you correct these and see if it's OK ?

Excel 2007 : RANKING TABLE #DIV/0! and #N/A

LinkBack

Thread Tools

Rate This Thread

Display

RANKING TABLE #DIV/0! and #N/A

Re: RANKING TABLE divo! and n/a problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions