+ Reply to Thread
Results 1 to 13 of 13

Calculate running percentage without sorting

  1. #1
    Registered User
    Join Date
    05-28-2009
    Location
    Oxford
    MS-Off Ver
    Excel 2007
    Posts
    86

    Calculate running percentage without sorting

    I'm trying to calculate the running percentage of cakes eaten by persons however, for reasons not obvious in this dummy data, the table cannot be sorted by cakes eaten.

    Please see attached. In Red is my running percentage formula, that works if the table is sorted by cakes eaten. However it cannot be sorted, so I need a formula that results in the column shown green.

    My aim is to automatically identify who is responsible for the top 80% of cakes eaten.

    Thank you.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-10-2012
    Location
    Derby, England
    MS-Off Ver
    2010 (Work) / 2016 (365) (Home)
    Posts
    14

    Post Re: Calculate running percentage without sorting

    Hi,

    Please see attached.

    The premise is basically:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The reason I've had to amend the formula slightly is to account for a tiebreaker for people who have eaten the same number of cakes. The tiebreaker I have used is arbitrary, but matches the order that you used to calculate the numbers in green.

    The "*10^-3" is arbitrary, but enough to cover the amount of people involved. If there were more than 100 people, you would have to change "10^-3" to "10^-6" or something larger (technically smaller, but you get the idea...)

    FYI, the Yes/No formula I have included is independent of the running total, but instead uses the PERCENTILE.EXC function. There is also a PERCENTILE.INC function; I think I have got the right one, but may be worth reading up on the two just to check because they gave me noticeably different answers.

    Hope this helps,

    ---
    Bernieburnham
    Attached Files Attached Files

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Calculate running percentage without sorting

    have a look at attached spreadsheet
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    05-28-2009
    Location
    Oxford
    MS-Off Ver
    Excel 2007
    Posts
    86

    Re: Calculate running percentage without sorting

    Thanks for your answers.

    Bernieburnham, this is the outcome I was looking for unfortunately I have no choice but to use excel 2007 and the _xlfn. prefix is not supported.

  5. #5
    Registered User
    Join Date
    05-28-2009
    Location
    Oxford
    MS-Off Ver
    Excel 2007
    Posts
    86

    Re: Calculate running percentage without sorting

    Would this do?

    Please Login or Register  to view this content.
    Edit: okay no that doesn't work
    Last edited by Knawl; 02-16-2017 at 04:50 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Calculate running percentage without sorting

    What about 'hiding' a sorted range on another sheet and referencing the results? In the attached workbook columns A and B of the table are copied to sheet 2 then sorted. The following formula, in column G (sheet 1) references the results:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Note that the three people indicated by Yes! in column F ate more than 50% of the cakes (192 of 280), therefore the threshold for the formula is 0.5
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    08-10-2012
    Location
    Derby, England
    MS-Off Ver
    2010 (Work) / 2016 (365) (Home)
    Posts
    14

    Lightbulb Re: Calculate running percentage without sorting

    Just realised that my original PERCENTILE formula was giving the right result but for completely the wrong reason and was a total fluke, so probably a good job is not an available solution for you. Try this instead:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    where column G contains the tiebreaker (as in my original spreadsheet).

    See attached for the full update. I have removed the helper column "Cakes Eaten Adjusted", as using SUMPRODUCT instead of SUMIFS make the helper redundant.

    Hope this helps.

    ---
    Bernieburnham
    Attached Files Attached Files

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculate running percentage without sorting

    @ Knawl

    Is there any chance some of your expected figures for duplicate cakes eaten are transposed?
    If not can you walk us through the logic? I can't make sense of their order.

    I can get the correct figures but not in the orders presented.

    In particular cells E6, E11 and E16. Also cells E13 and E15.
    Dave

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculate running percentage without sorting

    I went ahead with what I have. This uses no helper columns.

    The formula in In G2 and filled down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The differences between the proposed sort order of 'Running %' and my formula are:


    Cakes Eaten
    Running %
    4
    97.86%
    97.86%
    1
    100.00%
    100.00%
    3
    98.93%
    98.93%
    35
    81.07%
    81.07%
    5
    96.43%
    96.43%
    70
    25.00%
    25.00%
    2
    99.64%
    99.64%
    66
    48.57%
    48.57%
    6
    91.07%
    91.07%
    5
    92.86%
    94.64%
    8
    83.93%
    83.93%
    7
    86.43%
    88.93%
    56
    68.57%
    68.57%
    7
    88.93%
    86.43%
    5
    94.64%
    92.86%
    Last edited by FlameRetired; 02-19-2017 at 07:23 PM.

  10. #10
    Registered User
    Join Date
    05-28-2009
    Location
    Oxford
    MS-Off Ver
    Excel 2007
    Posts
    86

    Re: Calculate running percentage without sorting

    @FlameRetired - I have no idea why your is so close but not quite right. There are no expected figures, it's just dummy data for a running %age.

    @JeteMc - I thought about your suggested method using =IFERROR(LARGE(Table4[ParetoHelperOff],ROW(A1)),""). This combines the number of cakes eaten (in text) & a urn for each person (in text) into a number and then lists them by largest on another sheet. I then use index match to bring across the cakes eaten and calculate the running percentage. Then it gets looked up back into the original sheet. This is a bit processor heavy for thousands of rows but it works. This is going to be a template and I'm trying to minimise what the user has to do. I can't ask them to go through many steps because they will be easily put off.

    @bernieburnham - How did you generate the Tiebreaker column? This may be a less processor heavy solution if I can autogenerate the tiebreaker in a helper column.

    Many thanks to everyone.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Calculate running percentage without sorting

    I can help with the "minimize what the user has to do" part by offering two formulas that will automatically fill in columns A and B on sheet 2.
    The formula to fill column A is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula* to fill column B is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    * Indicates that the formula is array entered, meaning that Ctrl, Shift and Enter need to be pressed simultaneously, while the cell is in edit mode, in order to activate the formula.
    Let us know if you have any questions.
    Attached Files Attached Files

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Calculate running percentage without sorting

    @FlameRetired - I have no idea why your is so close but not quite right. There are no expected figures, it's just dummy data for a running %age.
    What is it that is not "quite right"? (I've been assuming it's the sort order.)

    Can you run me through the logic behind the sort orders of duplicates?

    At the moment they appear asynchronous.

    Perhaps I could rewrite my formula to be completely right.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Calculate running percentage without sorting

    I feel kind of like Forest Gump. I just reread your statement about "% age" and it struck me that these are birthday cakes that have been eaten, in other words ages. So it seems you are looking for the 3 people, 20% of 15, that are the oldest. That being the case the following formula works without any helpers, using only the info in columns A and B:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Running Percentage Formula
    By chico.corrales in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-19-2013, 04:42 PM
  2. Keep a running percentage
    By jonpackbosoxfan in forum Excel General
    Replies: 3
    Last Post: 03-21-2012, 12:04 PM
  3. Calculating a running percentage
    By Kevska in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-07-2012, 11:26 AM
  4. Replies: 2
    Last Post: 08-09-2008, 01:47 PM
  5. percentage:how do I calculate the percentage change
    By Knowledge001 in forum Excel General
    Replies: 3
    Last Post: 11-25-2005, 02:40 PM
  6. HOW DO I CALCULATE A RUNNING PERCENTAGE
    By fniguy24095 in forum Excel General
    Replies: 2
    Last Post: 05-04-2005, 04:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1