1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in complex formula calculation for presence work presence across months

Discussion in 'Ask an Excel Question' started by inddon, Nov 7, 2018.

  1. inddon

    inddon Member

    Messages:
    459
    Hello There,

    I have a requirement to calculate the presence of a person's availability based on working hours (FTE) on the start and an end date. The calculation is displayed across the months (Jan to Dec).

    I have uploaded the sample file.

    For ease of understanding, I have given range names to columns used in formulas. I tried with the first row Person Number=Donnie, found it very complex to go ahead.

    The sample file displays the exact way it should look. Here the individual months values needs to be converted into formulas.

    Very much appreciate your help in getting this done.

    Look forward to hearing from you.

    Regards,
    Don

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,005

    Attached Files:

    Thomas Kuriakose and inddon like this.
  3. inddon

    inddon Member

    Messages:
    459
    Thank you very much Bosco. It works as expected. I thought it would be a very complex one but you have made it look simple. :awesome::)

    In another worksheet it should reflect the summary of the Person Number wise (unique) listing and it's FTE Months. I will try it out myself, if I can get it working. Will keep you posted.


    Regards,
    Don
  4. inddon

    inddon Member

    Messages:
    459

    Hi Bosco,

    As a summary of the detail listing, I tried to get the unique listing of the persons and it's totals of the relevant columns against each person.

    Could you please have a look at the attached sample file and help me.

    Many thanks and look forward to hearing from you.

    Regards,
    Don

    Attached Files:

  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,005
    Unique person schedule summary in A30:Q40 with helper column in S9:S19

    1] In "Helper" S9, copied down :

    =IF(A9=A10,D9&", "&S10,D9)

    2] In Summary "Person number" A33, copied down :

    =IFERROR(INDEX(A$9:A$19,MATCH(0,INDEX(COUNTIF(A$32:A32,A$9:A$19),0),0)),"")

    3] In Summary "Start date" B33, copied down :

    =IF(A33="","",INDEX(B$9:B$19,MATCH(A33,A$9:A$19,0)))

    4] In Summary "End date" C33, copied down :

    =IFERROR(1/(1/LOOKUP(2,1/(A$9:A$19=A33),C$9:C$19)),"")

    5] In Summary "FTE" D33, copied down :

    =IF(A33="","",INDEX(S$9:S$19,MATCH(A33,A$9:A$19,0)))

    6] In schedule summary breakdown E33, copied across and down :

    =IFERROR(1/(1/SUMIF($A$9:$A$19,$A33,E$9:E$19)),"")

    Regards
    Bosco

    Attached Files:

    inddon and Thomas Kuriakose like this.
  6. inddon

    inddon Member

    Messages:
    459

    Thanks a lot Bosco for taking the time to help me out. :):awesome:

    Wish you a pleasant weekend.

    Regards,
    Don
  7. inddon

    inddon Member

    Messages:
    459

    Hi Bosco,

    There is a slight problem with the calculation of the formula in the detail section 1st part.

    I have attached the sample file for your reference. The 2 rows are marked in Bold. The one in green first row is working good. The second row formula marked in red is giving problem. I have given the explanation of the same in the file.

    I tried to modify the formula but couldn't, therefore seek your help.

    Could you please have a look.

    Look forward to hearing from you. Thanks again for your help

    Regards,
    Don

    Attached Files:

Share This Page