Calculate overall % based on different criteria

T

trimiii

Board Regular
Joined
May 15, 2018
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hi, I have the table with a total of 11 cells for 11 quizzes.
Example:
1. If all 11 tests were completed on the first attend then I mark as a "P" / overall % completion 100%
2. If 11 tests were failed on the first attend but completed on the second round, I mark as a P-F1 / overall % completion 90%
3. If 11 tests failed on 2nd time but completed on 3rd round then I mark P-F2 / overall % completion 80%
4. If all tests failed on the 3rd time but completed on the 4th round, then I mark as a P-F3 / overall completed 70%

How about if from the first round only 5 of 11 tests were completed on the 1st round, 3 of 11 on the 2nd round, 2 of 11 on 3r and 1 of 11 on the 4th round... what formula to use to calculate overall % (see table below)


[TABLE="class: x_MsoNormalTable"]
<tbody>[TR]
[TD="width: 42"]
[FONT=&quot]IPR 1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]IPR 2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]IPR 3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]IPR 4[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]IPR 5[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]IPR 6[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]IPR 7[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]IPR 8[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]IPR 9[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]IPR 10[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]IPR 11[/FONT]​
[/TD]
[TD="width: 81"]
[FONT=&quot]OVERALL
PROGRESS
(% COMP)[/FONT]​
[/TD]
[/TR]
[TR]
[TD="width: 42"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 81"]
[FONT=&quot]100.0%[/FONT]​
[/TD]
[/TR]
[TR]
[TD="width: 42"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 81"]
[FONT=&quot]90.0%[/FONT]​
[/TD]
[/TR]
[TR]
[TD="width: 42"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 81"]
[FONT=&quot]80.0%[/FONT]​
[/TD]
[/TR]
[TR]
[TD="width: 42"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 81"]
[FONT=&quot]70%[/FONT]​
[/TD]
[/TR]
[TR]
[TD="width: 42"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F1[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F2[/FONT]​
[/TD]
[TD="width: 41"]
[FONT=&quot]P-F3[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 50"]
[FONT=&quot]P[/FONT]​
[/TD]
[TD="width: 81"]
[FONT=&quot]?[/FONT]
​
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Try

=SUMPRODUCT(($A4:$K4=$O$1:$O$4)*($P$1:$P$4))

O1:P4 is the small table on the right

Code: 
[TABLE="width: 1392"]
<colgroup><col width="87" span="16" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87"]IPR 1

[/TD]
[TD="class: xl63, width: 87"]IPR 2

[/TD]
[TD="class: xl63, width: 87"]IPR 3

[/TD]
[TD="class: xl63, width: 87"]IPR 4

[/TD]
[TD="class: xl63, width: 87"]IPR 5

[/TD]
[TD="class: xl63, width: 87"]IPR 6

[/TD]
[TD="class: xl63, width: 87"]IPR 7

[/TD]
[TD="class: xl63, width: 87"]IPR 8

[/TD]
[TD="class: xl63, width: 87"]IPR 9

[/TD]
[TD="class: xl63, width: 87"]IPR 10

[/TD]
[TD="class: xl63, width: 87"]IPR 11

[/TD]
[TD="class: xl63, width: 87"]OVERALL

[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl63, colspan: 2"]PROGRESS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, colspan: 2"](% COMP)[/TD]
[TD][/TD]
[TD="class: xl63"]
P
[/TD]
[TD="align: right"]9.090909091[/TD]
[/TR]
[TR]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl64, align: right"]100.00%

[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="class: xl63"]
P-F1
[/TD]
[TD="align: right"]8.181818182[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl64, align: right"]90.00%

[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD="class: xl63"]
P-F2
[/TD]
[TD="align: right"]7.272727273[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl64, align: right"]80.00%

[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD="class: xl63"]
P-F3
[/TD]
[TD="align: right"]6.363636364[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl65, align: right"]70%

[/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P-F1

[/TD]
[TD="class: xl63"]P-F2

[/TD]
[TD="class: xl63"]P-F3

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]P

[/TD]
[TD="class: xl63"]?

[/TD]
[TD="class: xl66, align: right"]85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Table on right should be

Code: 
[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl63, width: 87"]P[/TD]
[TD="width: 87, align: right"]9.090909091[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F1[/TD]
[TD="align: right"]8.181818182[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F2[/TD]
[TD="align: right"]7.272727273[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F3[/TD]
[TD="align: right"]6.363636364[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Thanks a lot, it work just fine... you save me a lot of time mate.
 
Upvote 0
One more thing... for some reason when I convert to % it comes up as a: 7000%... I have changed in format cells but still same... thoughts?
[TABLE="width: 96"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
My bad,

Change the table to

Code: 
[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl63, width: 87"]P[/TD]
[TD="width: 87, align: right"]0.090909091[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F1[/TD]
[TD="align: right"]0.081818182[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F2[/TD]
[TD="align: right"]0.072727273[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F3[/TD]
[TD="align: right"]0.063636364[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
gaz_chops said:
My bad,

Change the table to

Code: 
[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl63, width: 87"]P[/TD]
[TD="width: 87, align: right"]0.090909091[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F1[/TD]
[TD="align: right"]0.081818182[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F2[/TD]
[TD="align: right"]0.072727273[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F3[/TD]
[TD="align: right"]0.063636364[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi again,
the formula works fine but I just have to ask this question... what formula did you use for this breakdown? I only can figure about 1st criteria :(
 
Upvote 0
Hi,

[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl63, width: 87"]P[/TD]
[TD="width: 87"]=1/11[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F1[/TD]
[TD]=0.9/11[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F2[/TD]
[TD]=0.8/11[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F3[/TD]
[TD]=0.7/11[/TD]
[/TR]
</tbody>[/TABLE]


hth

Gaz
 
Upvote 0
gaz_chops said:
Hi,

[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl63, width: 87"]P[/TD]
[TD="width: 87"]=1/11[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F1[/TD]
[TD]=0.9/11[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F2[/TD]
[TD]=0.8/11[/TD]
[/TR]
[TR]
[TD="class: xl63"]P-F3[/TD]
[TD]=0.7/11[/TD]
[/TR]
</tbody>[/TABLE]


hth

Gaz
Click to expand...

Yuppp, thanks a lot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
R
  • Question Question
Ranking based on 3 criteria (sports table)
Replies
1
Views
3K
DRSteele
DRSteele
T
  • Question Question
Count percentage completion based on criteriavertical vs horizontal
Replies
0
Views
230
trimiii
T
T
  • Question Question
Counting cells in a column in an array based on criteria
Replies
0
Views
898
tjdickinson
T
V
  • Question Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
787
Viau1337
V

Forum statistics

Threads
1,223,956
Messages
6,175,607
Members
452,660
Latest member
Zatman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top