• Status: Solved
• Priority: Medium
• Security: Public
• Views: 380

# Excel ranking a frequency with 0 errors as well

Hello all --

I am trying to rank a set of data in EXCEL -- the ranking element is actually a frequency of errors to volume.  Zero errors results in the volume becoming the frequency.  Trying to rank the frequency column while pushing the zero error ones to the top of the ranking.  I was do some IF formulas that would take the volume and multiply by a high number if there were zero errors and taking the original frequency of there weren't and ranking off of that but hoping there was something cleaner that I was mssing....

Sample Data :

ACTUAL

VOLUME      ERRORS      FREQUENCY      RANK

10000      5      2000      3
5555      15      370      5
7878      6      1313      4
90000      18      5000      2
150      0      150      6
6000      0      6000      1

DESIRED

VOLUME      ERRORS      FREQUENCY      RANK

10000      5      2000      4
5555      15      370      6
7878      6      1313      5
90000      18      5000      3
150      0      150      2
6000      0      6000      1
0
DonChart
1 Solution

SD-WAN SimplifiedCommented:
It looks like you do volume divided by error. So if error is 0 excel would do a divide by 0 error. So you could wrap it in iserror() and an if to do what you need. Can't think of anything cleaner at the moment. Something like:
If(iserror(volume/error),bignumberhere, (volume/error))
0

Commented:
If its only about the division, then you can solve it with a very simple way
add a column to the data, for example Column C
and set the value of first cell as
=IF(B1=0,1,B1)
If its 0 then the value of C1 will be 1
if not then it will have the same value as B1

then on Column D
=TRUNC(A1/C1)
then hide column C
and here you have it

if you are talking about the rank, then its time to use MACROS, I can write a one for you if you asked
0

Commented:
If all the zero error entries should be ranked highest then try this formula for D3 copied down

=IF(B3=0,SUMPRODUCT((B\$3:B\$8=0)*(C\$3:C\$8>C3)),SUMPRODUCT((B\$3:B\$8<>0)*(C\$3:C\$8>C3))+COUNTIF(B\$3:B\$8,0))+1

see attached

regards, barry
26912339.xls
0

Author Commented:
I thought that Sumproduct would get involved, not familiar with that at all, but after some adjusting to my range works great -- thanks for the help....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.