I have 2 columns of numbers. Column 1 is numbered from 1 - 10 in sequence. Column 2 can be in sequential order or mixed up.
It may look like this:
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
or it can look like this:
1 1
2 2
3 3
4 4
5 6
6 8
7 7
8 9
9 5
10 10
or it can look like this:
1 1
2 2
3 3
4 4
5
6 5
7
8 7
9 8
10 6
I want to get the percentage of the total numbers that match in each column. 1st example is a 100% match. 2nd example is 60%. 3rd example is 40% and it has blank spaces.
The actual formula would be great, but just the function that would perform this calculation would be helpful. I'm not even sure Excel can perform this operation.
To make it more complicated, in the 3rd example, the 5th stop was canceled, so I would consider it matching the number 5 in the left column...in other words, where there is a blank space on the right, I want to shift the numbers down on the left by 1 row. This would make the number 5 match in both columns. Then do the same thing for the next blank space.
(These are 10 stops that our delivery trucks have to do every day. Sometimes they have to cancel a stop, hence the blank spaces).
The goal is to see if our drivers are following the routing that is given to them every day...and if not, which percentage of deviation is there. We are developing this software and are working on giving our drivers the best possible routing, but if they don't follow it, it does no good. This formula will help us evaluate this.
Thank you!
Dave
Bookmarks