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

#### undergroundboy

##### macrumors newbie
Original poster
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
437.3 KB · Views: 1,726
• 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
226.5 KB · Views: 1,367

#### Nunyabinez

##### macrumors 68000
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
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.

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.