Can I get the the number of audits with perfect score of 100% to hit the goal

[joshlyg]

I have been trying to figure out how I can get the needed audits with 100% score.

Scenario 1:

I have 5 actual audits with an average of 75%, I need to get at least eight(8) 100% audits to hit the goal of 90%.



Scenario 2:

I have 11 actual audits with an average of 82.26%, I need to get at least twenty eight(28) 100% audits to hit the goal of 95%.



I tried to reverse sumproduct and use the Goal Seek but I don't know if I'm using them right.

Thank you!

 

[joshlyg]

**Another scenario

How many 100% audits do I need to hit 85.53% if given a total of 11 audits averaging 75.00%?

 

[Peter_SSs]

Welcome to the MrExcel board!

I get some slightly different answers to you.

22 03 04.xlsm
	A	B	C	D
1	Current No	Current %	No 100% Needed	Goal
2	5	75%	8	90%
3	11	82.26%	29	95%
4	11	75%	9	85.53%
Hit Target

Cell Formulas
Range	Formula
C2:C4	C2	=ROUNDUP((A2*D2-A2*B2)/(1-D2),0)

 

[joshlyg]

Welcome to the MrExcel board!

I get some slightly different answers to you.

22 03 04.xlsm
	A	B	C	D
1	Current No	Current %	No 100% Needed	Goal
2	5	75%	8	90%
3	11	82.26%	29	95%
4	11	75%	9	85.53%
Hit Target

Cell Formulas
Range	Formula
C2:C4	C2	=ROUNDUP((A2*D2-A2*B2)/(1-D2),0)

Thank you so much! But in case of the 4th row, do you think that C4's result should be 8? But hey, this is really a great help! Cheers!

 

[Peter_SSs]

in case of the 4th row, do you think that C4's result should be 8?

No, I don't think so. 8 would give you a result of 85.5263158% which is still marginally under your target of 85.53%.

If I remove the ROUNDUP function from my calculations you will see that both rows 3 & 4 almost make the target with 28 and 8 respectively but if you want to actually reach the target or above, you need one more audit.

22 03 04.xlsm
	A	B	C	D
1	Current No	Current %	No 100% Needed	Goal
2	5	75%	7.5	90%
3	11	82.26%	28.028	95%
4	11	75%	8.004837595	85.53%
Hit Target (2)

Cell Formulas
Range	Formula
C2:C4	C2	=(A2*D2-A2*B2)/(1-D2)

 

[joshlyg]

No, I don't think so. 8 would give you a result of 85.5263158% which is still marginally under your target of 85.53%.

If I remove the ROUNDUP function from my calculations you will see that both rows 3 & 4 almost make the target with 28 and 8 respectively but if you want to actually reach the target or above, you need one more audit.

22 03 04.xlsm
	A	B	C	D
1	Current No	Current %	No 100% Needed	Goal
2	5	75%	7.5	90%
3	11	82.26%	28.028	95%
4	11	75%	8.004837595	85.53%
Hit Target (2)

Cell Formulas
Range	Formula
C2:C4	C2	=(A2*D2-A2*B2)/(1-D2)

You are absolutely right!! This is way beyond my expextations!! You really are a genius!!! Thank you, sir!!

 

[Peter_SSs]

You're welcome.