+ Reply to Thread
Results 1 to 13 of 13

Compliance Actions Formula

  1. #1
    Registered User
    Join Date
    01-12-2021
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Compliance Actions Formula

    Hello,
    I was hoping for some assistance with a formula and hope I have explained it properly.
    I am currently doing some compliance for work and what they have asked me to do is only mark something as non-compliant when it has not been actioned accordingly.
    For example, I have Adherence to XXXX , I would normally just mark this as non-compliant and move on but what they want to see is it first marked non-compliant but when it has been addressed/action that negates the non-compliance and marks it again as compliant. I have tried to come up with something in this excel chart but am totally lost with what formula to use and appreciate any guidance. Cheers
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Compliance Actions Formula

    Maybe this in E3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Good luck!

  3. #3
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Compliance Actions Formula

    Maybe.

    =IF(C3="No","Yes",IF(C3="Yes",IF(D3="Yes","Yes","No"),""))

    Regards.

  4. #4
    Registered User
    Join Date
    01-12-2021
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Compliance Actions Formula

    Thank you, that is beginning to work.
    I was just wondering if there is a way to then translate the compliant "yes" to an overall percentage rating?

  5. #5
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Compliance Actions Formula

    What is the expected result and in what what cells?
    It would help if you would post a new example workbook with results mock-up manually.
    Last edited by Estevaoba; 09-14-2021 at 11:00 PM.

  6. #6
    Registered User
    Join Date
    01-12-2021
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Compliance Actions Formula

    This is what I was using, it was just from a template I found online and i adapted it to work for what I needed, none of the formulas were entered by me. Now, manager wants only to see it being marked non-compliant if it hasn't been addressed.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Compliance Actions Formula

    Maybe E8

    Please Login or Register  to view this content.
    Regards.

  8. #8
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Compliance Actions Formula

    In your first example sheet, try this in a blank cell of your choosing:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-12-2021
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Compliance Actions Formula

    I have attached another sheet of how I would like it to look like ideally, again not sure if it will be possible to achieve this but I would like to be able to, for example - if I mark something as not compliant with a NO and there is no corrective action implemented with a NO, then that is only when the percentage score is reduced and reflected in D3. I would then like the overall compliance to be reflected in B19 for example....
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Compliance Actions Formula

    Please check if this will work.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-12-2021
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Compliance Actions Formula

    I think this is really close and overall I think it is working but after reviewing it I think it would make more sense if we switched the columns around to read Non-Compliance in B4, the corrective action in C4 and then finish with Compliant in D4

    Is it at all possible to add in a N/A (Not Applicable) for risks that arent marked but those to not affect the overall compliance score?

  12. #12
    Registered User
    Join Date
    01-12-2021
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Compliance Actions Formula

    Also, is there a way from the entries for non-compliance, say if it is a no and then there is a yes in corrective action, it automatically puts a yes in the compliant column?

  13. #13
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Compliance Actions Formula

    The formula in column B does exactly as pointed out in your post #12.
    As for design, you can switch columns around as you wish, as long as you adjust the formula accordingly.
    And as for adding "N/A" alternative, it will not affect the compliance score if you adjust the formula in D3 like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. Time formula to calculate compliance
    By manito2000 in forum Excel General
    Replies: 9
    Last Post: 12-27-2017, 12:15 PM
  2. formula for compliance
    By ailismanzoni in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2017, 11:21 AM
  3. Formula to calculate service level compliance
    By ljerromes in forum Excel General
    Replies: 3
    Last Post: 04-10-2016, 06:12 PM
  4. Formula to calculate compliance
    By patfle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-16-2013, 11:54 AM
  5. Formula to calculate compliance
    By patfle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-16-2013, 11:41 AM
  6. [SOLVED] Help with Formula for % Compliance
    By Maeeday in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2013, 08:01 PM
  7. driver hour compliance formula mto
    By hello? in forum Excel General
    Replies: 1
    Last Post: 02-11-2005, 03:06 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