+ Reply to Thread
Results 1 to 12 of 12

Formula to get actual numbers upon meeting conditions

  1. #1
    Registered User
    Join Date
    04-09-2017
    Location
    UAE
    MS-Off Ver
    MS365 Version 2301
    Posts
    49

    Formula to get actual numbers upon meeting conditions

    Need help please. Scenario is, in a department:
    • there's certain KPI activities that have weekly target depending on your role.
    • If someone is on leave (supervisory and above role) they need to have a delegate assigned. This delegate will need to do the KPI activities for them while on leave
    • KPI activities done by non-supervisory role should not be counted if they are not assigned as delegate during the dates they have completed the KPI activity

    Given the above conditions, is it possible to get the actual numbers of completed KPI without having to count them manually?

    Attached sample file has been updated with expected results.
    Attached Files Attached Files
    Last edited by majana54; 05-03-2024 at 04:49 AM.

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

    Re: Formula to get actual numbers upon meeting conditions

    This proposal is adds some columns to the original setup.
    1. G21:G22 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. O5:O22 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3. P5:P22 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    4. X2:X19, which will replace V2:V19, are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    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.

  3. #3
    Registered User
    Join Date
    04-09-2017
    Location
    UAE
    MS-Off Ver
    MS365 Version 2301
    Posts
    49

    Re: Formula to get actual numbers upon meeting conditions

    This approach gave me a workaround. Thank you so much JeteMc!

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

    Re: Formula to get actual numbers upon meeting conditions

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  5. #5
    Registered User
    Join Date
    04-09-2017
    Location
    UAE
    MS-Off Ver
    MS365 Version 2301
    Posts
    49

    Re: Formula to get actual numbers upon meeting conditions

    Quote Originally Posted by JeteMc View Post
    This proposal is adds some columns to the original setup.
    1. G21:G22 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. O5:O22 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3. P5:P22 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    4. X2:X19, which will replace V2:V19, are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    I'm sorry to ask but I can't figure it out. (I'm not entirely good with index) is there a way that if an employee (delegated person) is not included in the list of employees who have KPI's to hit, their KPI count will be added even thought the target has been exceeded?

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

    Re: Formula to get actual numbers upon meeting conditions

    I feel like the employee would need to be listed in the Finance Employee List section, however if you could attach a file illustrating the request, along with expected results, to your next post someone may be able to think of a way to help.

  7. #7
    Registered User
    Join Date
    04-09-2017
    Location
    UAE
    MS-Off Ver
    MS365 Version 2301
    Posts
    49

    Re: Formula to get actual numbers upon meeting conditions

    Quote Originally Posted by JeteMc View Post
    I feel like the employee would need to be listed in the Finance Employee List section, however if you could attach a file illustrating the request, along with expected results, to your next post someone may be able to think of a way to help.
    I do have a "Main Table of all employees" (A4:F7). Some employees don't have KPIs but are assigned as delegates.

    Condition are:
    1. if the delegated employee does not have a KPI to complete, the count of completed KPIs will be added to the person they are delegated to regardless if the target has been reached.
    2. However, if the delegated employee has a KPI of his/her own to complete, once the target of the person they are delegated to has been reached, the remaining KPIs will be added to the delegate's count.

    Please see attached file's column V shows the expected results.
    Attached Files Attached Files

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

    Re: Formula to get actual numbers upon meeting conditions

    I believe that changing the formula in column P to read as follows will work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: Looking back at point 2 in post #1 (If someone is on leave...) that as the supervisor was not on leave on 7-Jan it would seem that KPI would not get assigned.
    Let us know if you have any questions.

  9. #9
    Registered User
    Join Date
    04-09-2017
    Location
    UAE
    MS-Off Ver
    MS365 Version 2301
    Posts
    49

    Re: Formula to get actual numbers upon meeting conditions

    Quote Originally Posted by JeteMc View Post
    I believe that changing the formula in column P to read as follows will work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: Looking back at point 2 in post #1 (If someone is on leave...) that as the supervisor was not on leave on 7-Jan it would seem that KPI would not get assigned.
    Let us know if you have any questions.
    Thank you for the response JeteMc. I must admit I am having issues trying to fit the solution in my actual file as I don't understand fully how the formula works.

    With this being said, I incorporated the formula to my actual file (attached). The count still stops even when the delegated employee is not part of the actual employees who have a target KPI to complete. Please can you check when you have a moment.
    Attached Files Attached Files

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

    Re: Formula to get actual numbers upon meeting conditions

    Try the following in cells S3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  11. #11
    Registered User
    Join Date
    04-09-2017
    Location
    UAE
    MS-Off Ver
    MS365 Version 2301
    Posts
    49

    Re: Formula to get actual numbers upon meeting conditions

    Quote Originally Posted by JeteMc View Post
    Try the following in cells S3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    I cannot express how grateful I am to you JeteMc. Thank you so much it works exactly how I needed it.

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

    Re: Formula to get actual numbers upon meeting conditions

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Replies: 17
    Last Post: 08-10-2019, 02:04 PM
  2. [SOLVED] Match and index formula to be used to get the figure from array by meeting 3 conditions
    By srinivastsc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-19-2017, 10:26 AM
  3. pasting results of a formula as actual numbers
    By kitbit in forum Excel General
    Replies: 3
    Last Post: 11-14-2007, 09:57 AM
  4. [SOLVED] track calls, meeting, proposal vs actual sales
    By brad in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-30-2006, 12:50 AM
  5. [SOLVED] Meeting two conditions before summing
    By Andeb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  6. [SOLVED] Meeting two conditions before summing
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  7. [SOLVED] Meeting two conditions before summing
    By Andeb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

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