Results 1 to 4 of 4

Thread: Help with calculating percentages

  1. #1
    Banned
    Join Date
    Jul 2023
    Posts
    1
    Rep Power
    0

    Question Help with calculating percentages

    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

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    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

  3. #3
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    94
    Rep Power
    12
    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.
    Attached Files Attached Files

  4. #4

Similar Threads

  1. Monthly Chart with Values & Percentages
    By msiyab in forum Excel Help
    Replies: 7
    Last Post: 01-08-2015, 03:44 PM
  2. Calculating data from other Worksheets
    By Ribice in forum Excel Help
    Replies: 4
    Last Post: 12-27-2013, 11:32 AM
  3. Calculating Subtotals Within Groups of Data Using UNION
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 1
    Last Post: 05-24-2013, 11:54 AM
  4. Calculating Ratio Within Groups using Self Join
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-21-2013, 07:52 PM
  5. Effective way of calculating the difference
    By zzzqinzzz in forum Excel Help
    Replies: 0
    Last Post: 12-13-2012, 03:39 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •