Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

undergroundboy

macrumors newbie
Original poster
Feb 18, 2016
1
0
Hello!

I just recently got a new job as an analyst and I am having trouble calculating the percentage of matches of specific names (i.e. Albany and Eden, Elkton and Golden) between two columns. Attached a sample problem if anyone can help guide me? I have a general idea of using match and IF but don't know how to start exactly. Thank you!
 

Attachments

  • Screen Shot 2016-02-18 at 4.06.50 PM.png
    Screen Shot 2016-02-18 at 4.06.50 PM.png
    437.3 KB · Views: 1,726
  • Screen Shot 2016-02-18 at 4.06.47 PM.png
    Screen Shot 2016-02-18 at 4.06.47 PM.png
    285.8 KB · Views: 1,506
  • Screen Shot 2016-02-18 at 4.06.45 PM.png
    Screen Shot 2016-02-18 at 4.06.45 PM.png
    226.5 KB · Views: 1,367

Nunyabinez

macrumors 68000
Apr 27, 2010
1,758
2,230
Provo, UT
You need to use the COUNTIF() Function and the exact() function.
Here is how to do it. In an empty cell enter =EXACT(<first cell>,<second cell>) this will return True if they are exact and False if they differ in any way.
Then in the cell next to that put =COUNTIF(<cell with the exact formula>,TRUE)
Drag the two formulas to the end of your columns.
Then you will have a column with Trues and Falses and next to it a column with 1s and 0s. To get a percentage you just need to take the total number of 1s, divided by the total number of entries and that will give you the percentage of cells where the two values match. I assume you can figure that part out by yourself.
 

Moakesy

macrumors 6502a
Mar 1, 2013
576
1,209
UK
If I understand your screenshots correctly, you need amounts (well, percentage) by courier, by destination yeah?

So it's two dimensional data......a table that shows carrier down the side, destinations across the top? Or even by each carrier, by from and two location. If so, a pivot table will be the quickest way to create the data, which you can then use to calculate percentages.

I notice the brief also says about a date range....how important is this?

All of it is do-able, and could be surprisingly quick. Also, what version of excel are you using, as later versions can do some sexy stuff that looks amazing but is a piece of cake! It's an easy way to make a great impression in your new job. :D


EDIT : Just notice that they also have left a column blank on the Actuals tab, assume they want you to use WEEKNUM function in there? If so, then put =WEEKNUM(a2) in cell B2 and copy it down. This will look at the date in A2 and say if it's week1, week 2 etc of the year.
 
Last edited:
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.