Calculating percentages

[muna]

Hi all,

I have spent some time making a dummy spreadsheet so that it is as easy as possible to explain the solution I am looking for.

In my dummy spreadsheet (see attached) I have left notes of what needs to be done but I will explain again. I need percentages that shows a breakdown by directorates and destinations. The percentages shouldn't be about wrongly classified cases when compared in total but just for like-for-like comparisons i.e. Customer Design should be compared by itself and not how many cases have been misclasified in total. If it can be done without adding an extra column such as 'Original Directorate' then that would be great. This is a real challenge!

In the attachment, ignore the comments about the conditional formatting help required. I have posted a seperate thread on that issue as it's a seperate from the percentages help I require. I think this is most sensible of me to do as it will help with the site's search function and possibly other users.

 

[muna]

Thank you for your help in advance.

 

[GraH - Guido]

To get you going... B4 D4 F4 H4 formula filled. Seems to be working.
in B4: SUMPRODUCT(1*NOT(ISERROR(FIND(B$3,$E$18:$J$31,1))))/COUNTA($B$18:$B$31)
and copy across. Not sure I understand yet what the other % are all about.

 

[muna]

To get you going... B4 D4 F4 H4 formula filled. Seems to be working.
in B4: SUMPRODUCT(1*NOT(ISERROR(FIND(B$3,$E$18:$J$31,1))))/COUNTA($B$18:$B$31)
and copy across. Not sure I understand yet what the other % are all about.

Thank you for your hard work on this.

I have attached a new file and added an additional column (original directorate column) which I believe would make the task easier.

Can you help?

 

[bosco_yip]

Thank you for your hard work on this.

I have attached a new file and added an additional column (original directorate column) which I believe would make the task easier.

Can you help?

1] Revised your additional Column R "Original directorate" formula to :

=SUBSTITUTE(RIGHT(E18,LEN(E18)-SEARCH("-",E18)),CHAR(10),"")

2] In B4, formula copied to D4, F4 and H4

=COUNTIFS($R$18:$R$32,B$3)/COUNTA($B$18:$B$32)

Regards
Bosco

 

[muna]

1] Revised your additional Column R "Original directorate" formula to :

=SUBSTITUTE(RIGHT(E18,LEN(E18)-SEARCH("-",E18)),CHAR(10),"")

2] In B4, formula copied to D4, F4 and H4

=COUNTIFS($R$18:$R$32,B$3)/COUNTA($B$18:$B$32)

Regards
Bosco

Thank you for your efforts but the percentage is wrong as per my example that compliance should be 20%.

Also, could you try to work out Charlton?

 

[bosco_yip]

Thank you for your efforts but the percentage is wrong as per my example that compliance should be 20%.

Also, could you try to work out Charlton?

Then,

In respect to "Final destination", using "Final directorate" (Col S) as the Lookup range.

1] In "Final directorate" S18, formula copied down :

=SUBSTITUTE(INDEX(TRIM(H$9:H$15),SUMPRODUCT(ISNUMBER(SEARCH(P18,J$9:M$15))*(ROW(H$9:I$15)-ROW(H$8))))," ->","")

2] In B4, formula copied to D4, F4 and H4

=COUNTIFS($S$18:$S$33,B$3)/COUNTA($S$18:$S$33)

p.s. How do you get "compliance" =20% ?

My calculation of "compliance" =5/16 =31.25%

Regards
Bosco

 

[muna]

Then,

In respect to "Final destination", using "Final directorate" (Col S) as the criteria.

1] In "Final directorate" S18, formula copied down :

=SUBSTITUTE(INDEX(TRIM(H$9:H$15),SUMPRODUCT(ISNUMBER(SEARCH(P18,J$9:M$15))*(ROW(H$9:I$15)-ROW(H$8))))," ->","")

2] In B4, formula copied to D4, F4 and H4

=COUNTIFS($S$18:$S$33,B$3)/COUNTA($S$18:$S$33)

p.s. How do you get "compliance" =20% ?

My calculation of "compliance" =5/16 =31.25%

Regards
Bosco

Compliance could have only been correctly classified 5 times. I only got it correct four times so wrong once. For this reason, I have m misclassified 20% of cases.