Jun 09 2021 03:32 AM - edited Jun 09 2021 06:34 AM
Hello,
Does anyone know how to overcome issues with ranking duplicate values in a data set when trying to generate a pareto chart?
I have attached an example of the data set I am working with. I have used the RANK function to order the data, however when duplicate values appear the VLOOKUP function will only return one the of the ranked duplicate values. In the example, where the value '30' appears twice, the VLOOKUP only pulls one of these across and leaves the other. When generating a pareto chart I then have missing data.
Any help here would be greatly appreciated.
Thank you,
Beth
Excel version: 2008
Operating system: Windows 64-bit
Jun 09 2021 04:26 AM
The values in column C refer to another workbook, so we see #VALUE!
Jun 09 2021 06:35 AM
Jun 09 2021 07:55 AM
@Bethany1993 Why bother ranking? Perhaps you can work with a solution like in the attached workbook.
Jun 09 2021 08:01 AM
In the attached version, I added tiny random disturbances to the data. I left the column of random numbers that I used for your information, but it is not needed any more. The disturbances break the ties.
They are hidden by formatting the numbers with 0 decimal places.
Sep 17 2021 05:13 AM
Hopefully the attached document will help you.
You can try here for Microsoft Excel & Office 365.
And if you are comfortable with Google Sheets, you can try here.
Thanks