Formula for calculating totals for different months in a list as a percentage?

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi folks


    My aim is to show the totals as a percent, how long it takes people from applying for a course to attending .


    I have a list of two dates:

    An Application Date (the date contact is made to attend a training course) and the Course Date (the date of the course).


    From a list of Course Dates in a given month, (in this example March 2019) I want to calculate the difference between the Application Date and the Course date, for each candidate in months and then group those totals by months and shown as a percentage.


    At the moments I'm typing this manually and I'm looking for a formula that can work these numbers out automatically.

    Instead of keep typing manually I was trying to use the CHOOSE formula to give the months in Column F but don't know how to select just one month consecutively from the list so maybe I'm going the wrong way with that ?


    Any formula guru's out there able to assist?


    1. Find the month of course dates from Column B Displayed in F8 - I used =TEXT(B2,"MMM") function.

    2. Find how many course places there were for that month - =SUM(B:B) varies depending on data loaded into columns A and B

    3. Group Column A by month and find totals for each month displayed in F, G - (I just count manually)

    4. Calculate as a percentage of all course dates, each total for each month H - simple calc based on above)

    5. Show totals for each month and as a percentage - as per sheet


    Many thanks


    Smudge

    Hello,


    Not sure what is exactly your starting point ... since your formula in cell F1 looks fine ...


    Is the first formula you are looking for ... the one for cell F2


    Code
    =TEXT(DATE(2020,MONTH(1&F2)+1,1),"mmm")


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    You are welcome :)


    Looks like you want to go without a Pivot Table ... and only with formulas ...


    So you can take a look at attached test file ...


    Hope this will help

    :)

    Thanks a lot for your Thanks ...AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Carim


    Thanks for taking the time to answer my question. I haven't seen Sumproduct before - (I was messing around trying to use Min & Max formulas looking for the start and end dates).


    Your response is very helpful - complete with added pivot table too ;)


    Many thanks


    Smudge

    You are more than welcome :)


    The Sumproduct function is ... a lot of fun ... since it is very flexible ...


    If you feel like digging a little bit :


    https://exceljet.net/excel-fun…excel-sumproduct-function


    Cheers

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login