Help with calculating percentages

**Abelardus** · 07-07-2023, 06:44 AM

Hello,

I have a spreadsheet with hundreds of vendors/suppliers. Each vendor delivers multiple parts, for which some are delivered "Early", some "Late," and some "On-Time."

Some vendors deliver the same type of part, so each vendor delivers it at different times, falling into either of the above delivery statuses independently.

The Delivery Status column/field shows the status, but it is actually a formula that is calculated using two other columns with dates.

THE QUESTION: I need to calculate the percentage of each delivery status ("Early", "Late," and "On-Time") for each vendor rather than the percentage of status as a whole.

E.g., Vendor A delivered "On-Time" x% out of the total of all the "On-Time" deliveries done by all vendors together (I'm debating whether I should get the percentage out of all the total deliveries regardless of the type of status as the denominator for the calculation rather than only of the total of each status. Please suggest the right denominator).

I need the percentages of each delivery status per vendor to be able to identify the "Top 10" vendors that delivered "Early", top 10 that were "Late", and top 10 "On-Time."

This is a perpetual spreadsheet that will be refreshed constantly. Below is how the data is structured in the spreadsheet.

Your help is very appreciated,

Abelardus

Vendor Delivery Performance.JPG

VENDOR PART ID DELIVERY STATUS
Vendor A Part A Late

Part B On-Time

Part C Late

Part D Early
Vendor B Part A On-Time

Part E Late
Vendor C Part A Late

Part X Early

Part Y Early
Vendor D Part X On-Time

**DocAElstein** · 07-07-2023, 02:22 PM

Hello Abelardus
Welcome to ExcelFox

Something like this can probably be done in endless different ways in Excel and Excel VBA.
I don’t think I would want to try and do all you want with formulas in Excel as that would be very complicated and messy with a lot of complicated long formulas.

In Excel VBA for these sort of requirements, we often use some ideas based on Dictionary List type ideas. It’s a thing a bit external to Excel which we can use in Excel. It is a bit of a coincidence that it is useful for such requirements, because it is good for making Lists of things from diverse lists and getting them in some order, especially where there may be some unique things like unique headings, names, part numbers, or other things you might at some point in the calculation you may want to put against/ sum against/ attribute to some unique thing like your …. "Early", "Late," and "On-Time."

It is difficult for me or anyone to know where to start helping you as I know nothing about you and your current knowledge of Excel and VBA.

For starters, I suggest you take a look at these links, and then let us know if anything there makes any sense and how far you are in understanding how to start using anything there in your requirement.

Also, if you could provide a workbook sample with data similar to that you gave but slightly more, then that could be helpful. But not too much more data – it is good in a forum question not to supply too large a sample. It is a good sample actually that you gave for us to work with, assuming that it represents typical data structures and scenarios.

This is the Dictionary List stuff I am talking about
https://excelmacromastery.com/vba-dictionary/
( https://www.snb-vba.eu/inhoud_en.html )
https://www.snb-vba.eu/VBA_Dictionary_en.html
https://www.snb-vba.eu/VBA_Arraylist_en.html
https://www.snb-vba.eu/VBA_Sortedlist_en.html

Alan

P.S. Its not too obvious how to attach files here. Here are some notes on that.
Attachments to posts at ExcelFox: https://excelfox.com/forum/showthrea...ll=1#post11279
https://excelfox.com/forum/showthrea...ll=1#post15589

Alternatively you can upload a file to a file sharing cloud place and give us the share link

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**p45cal** · 07-11-2023, 08:21 PM

One way in the attached is with Power Query output to a pivot table.
The pivot table is based on a transformation done in the background by Power Query (built-in to Excel from version 2016) which essentially takes your table, fills in the blanks, removes the Part ID column then works out the percentage of delivery statuses for each vendor.
This feeds the pivot table where:
The vendors are sorted by higher percentages at the top, and currently shows only the top 4 vendors (more if there's a tie at the bottom-performing vendors). These are standard pivot table features which you can tweak yourself.
There's a slicer which allows you to choose which statuses you want to see; I've left it showing both Early and On-time percentages to let you see which vendors are most likely to deliver at least on time or earlier, best performing at the top.
I've also added a Never status for one row in the source data. If you have other statuses, the pivot will automatically show those too.

The only thing you need to do after changing the source table is to refresh the pivot by right-clicking it and choosing Refresh.