Solved

Excel ranking a frequency with 0 errors as well

Posted on 2011-03-25
4
345 Views
Last Modified: 2012-05-11
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
Comment
Question by:DonChart
4 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35217932
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
 
LVL 4

Expert Comment

by:mawni
ID: 35218054
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35218109
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 Closing Comment

by:DonChart
ID: 35231791
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question