Readjust weighting based on non compliance

[fmluder93]

Hi there,

I have an audit tool that is set up to weight results based on what level of compliance is obtained. The tool now needs to include an additional weighting for when people get a 1 in the score (which is not meeting compliance). This is only for those that are highlighted in red cells (there are about 11 in total). These are rows 14,19,25,33,36,39,44,46,53,54 and 60 in the Main Audit tab.

At the moment auditees can get a leadership or advanced score even if they have 4 or 5 non compliance results (ie score 1 in the red cells as in column F). Im not sure how to do this.

The way i want the tool to work is the grading to be readjusted to the following:

1) If they get up to 3 non conformances they go straight to transition result
2) If they get 4 plus non conformances they go straight to critical result

I have attached the tool that is used. Im working off the Main Audit tab and Audit Summary tab. Im not too concerned with amending the actual weight as yet - as that is something i will work on next. Or if anyone has any ideas about how to go about that - that would be appreciated.

Many thanks in advanced.

[FDibbins]

Hpow do you determine if they get a 1 or not? is that data entered manually in F14 on the main audit sheet?

[fmluder93]

Yes it is entered manually by the auditor on the main audit sheet. We esentially sit down with the auditee and ask them the questions on the main audit sheet. If the auditee cannot provide evidence they are assigned a 1 score. If they do, then the auditor will give them a 2.

[FDibbins]

Ok, im not sure if I follow where you want this "result" to show, but I added the following formula to P13. It checks the value in Q13, and then returns the reult based on what you said above. If this is not where you want it to be, and you cant figure out how to modify the formula, let me know and we can look at it again. Hope this helps

=IF(AND(Q13>0,Q13<4),"Transition",IF(Q13>=4,"Critical",IF(Q13=0,"Comply","Non Compliant")))

[fmluder93]

Thanks muchly. Do you have the document ??? I'll try and see if i can do it based on what you have suggested - but want to make sure i have it right.

[fmluder93]

Sorry - by the way - the result needs to show in the Audit Summary in C/D (merged cells) 24 (where you see Leadership)

[FDibbins]

Trying to follow the logic through your process. Can you walk me thru how you have things set up plz, like what would cause what to change, what that would change etc, and where your final answer need to be?

[fmluder93]

Hi there. I have attached the sample document again with more description of where i need help. Step by step:

Essentially what we need to do be able to do, is have the grading / scoring adjusted in the Audit Summary based on the count of non compliant critical items there are. These are demonstrated in D14 to D17 with the total in D18. This is where the data will come from to determine the actual audit result (advanced etc).

Based on D18, if there are 1 to 3 in the cell, then C24 should be adjusted to Transition and cell E18 adjusted to 2.

Then if there are 4 or more in D18, then C24 should be adjusted to Critical and cell E18 adjusted to 1.

Some cells have a VLOOKUP function, which is how the result is generated (which is from the Scoring sheet). Im wondering whether there is a way to also add an IF function to this. So the end result would either be:

1. The actual score with no critical non comply – so nothing will need to be changed and the VLOOKUP would still be relevant or
2. The actual score readjusted based on the criteria above (1 to 3 = transition and 4+ = critical) and the VLOOKUP not applied.

I hope this has given you enough clarification. And any help you can provide is GREATLY appreciated.

[FDibbins]

Getting what you need doesnt seem to be a problem, I just have to figure out where you want what lol. I have the 1st part done, working on the last part. See the attached to make sure I am still on the right track

[fmluder93]

This is PERFECT and exactly what i needed. At least i eventually got out what i needed to say !!! FANTASTIC !!!!!!

[FDibbins]

Happy to help, but what did you want to do with the last part of you question (I cant find where you asked it, so maybe there wasnt a last part?)?

If this answers your question, please mark this post as solved, and shout if you need any further assistance

[fmluder93]

All is good with the adjusted grading now - and again THANK YOU SO MUCH for that. I have one more question. How do you go about changing the actual percentage score in B24 in the Audit Summary. I dont want to go changing the weighting in the main audit sheet, however was thinking of putting in another IF function. I tried a few variables such as +IF(D18=1,'58'), IF(D18=2,'47' etc but that didn't work (I also varied with ""). Then tried conditional formatting, but again that didn't work. I would like the score in B24 amended as follows:

Number of critical items in Audit Summary sheet D18 =
1 then score is 58 for B24 in Audit Summary sheet
2 then score is 47 for B24 in Audit Summary sheet
3 then score is 40 for B24 in Audit Summary sheet
4 then score is 33 for B24 in Audit Summary sheet
5 then score is 31 for B24 in Audit Summary sheet
6 then score is 27 for B24 in Audit Summary sheet
7 then score is 23 for B24 in Audit Summary sheet
8 then score is 19 for B24 in Audit Summary sheet
9 then score is 15 for B24 in Audit Summary sheet
10 then score is 11 for B24 in Audit Summary sheet
11 then score is 7 for B24 in Audit Summary sheet

I promise this is the last thing I need .....

[FDibbins]

I put all your data into a table and then used =vlookup() to find the answer. If there is any situationthat you need the original value to be displayed, just +b18 to the bottom of the table, and what the value needs to be. I couldnt put it on another sheet (all protected), so i put it on audit summary...move it if you want and change the references as needed.

let me know if you need anything else, i am happy to help

[fmluder93]

This is perfect !! THANK YOU THANK YOU THANK YOU !!!!

[FDibbins]

Again, glad to help, shout if you need anything else

[FDibbins]

oh By the way...If you need to add any other categories to what I just gave you, simply add them to the table and extend the range for the vlookup. I did it that way to give you the flexibility to change the variables, if you want