Hello, can someone help me devise a formula to meet this need:

I am working on a project where I am helping managers manage their sales force's quality scores. The current minimum score requirement is 87.5% which is to say that they have to have 87.5% of the maximum possible points

Each unit has a maximum possible points of 25, so a unit with 25 points has a score of 100% for that unit.The sheet has the user input the amount of units they have in cell B3 and also inputs their current score in C3

I need 2 formulas, 1 that calculates how many more units they need with a 100% to meet the minimum score of 87.5% (in cell B4) and the other to show me the new score (in cell C4)Example user enters 5 units in cell B3 and enters their current score of 84% in cell C3, so the formula in B4 would calculate that they need 2 units and the formula in C4 would calculate their new score of 88.57%

message edited by mecerrato

Call me thick, but I have no clue how your numbers fit together. I don't understand your "units" vs "points" explanation.

If 1 unit = 25 points = 100%, that's 4% per point. How does 5 units = 84% and how does 7 units = 88.57%?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

every unit (loan) has the possibility of earning 25 max points which is a perfect score of 100%, so if your score is 84% for 5 units (loans) than you did not achieve 100% (25 points) on each unit (loan) you actually achieved 105 points ((5*25)*84%) In this example you need 6 units (loans) with perfect scores (each one with 25 points) to get the minimum score of 87.5% or better

ColumnA ColumnB

Loans 20

Current 84.00%

Target 87.50%

Loans Needed 6

New Score 87.69%I actually kind of got it working but have a couple of issues:

issue #1:

**** New Score formula - I had to use 2 helper cells because I couldn't get the formula to work together, here is the formula I would like to use

=((B1*25)*B2)+(B4*25)/((B1+B4)*25) but the math does not work so I broke up the formula into 2 different cells and then divided them using =F1/F2 this worked but I prefer to not have to use 2 helper cellsissue #2: the formula has something wrong, the below sample of data should result in 7 loans needed to get to target minimum score of 87.5% but it is resulting in 8

Loans needed =(CEILING(B1*(B3-B2)/(1-B3),1))ColumnA ColumnB

Loans 25

Current 84.00%

Target 87.50%

Loans Needed 8

New Score 87.88%what is odd is that all other combination of loans and current score work fine (at least the 10 or 15 combinations I tested)

Never mind on issue 1 I just figured it out, I was missing a parenthesis, corrected formula is =(((B2*25)*B3)+(B5*25))/((B2+B5)*25)

In your second formula, the use of the CEILING() function may be your problem. The ceiling function rounds a number

upor away from zero, to the nearest multiple specified by the user.In your formula, you specify 1,

so if your result is even .00000000000001 over the whole number,

IE 7.00000000000001

the CEILING() function rounds it up to 8You might want to try one of the other rounding functions and see how they work.

MIKE

Try this one in place of your formula using CEILING(): =ROUND(ROUNDUP(B1*(B3-B2),3)/(1-B3),0)

It seems to work, but no guarantees.

MIKE

Thank you mmcconaghy , you are always reliable :-)

I am using the same concept for a different data set, this one has no points,just evaluates the number of "passes". In this example the score is 50% from 10 loans which means only half passed. the "Loans W/Perfect Scores Needed" should result in 5 which would get us above the target of 65.00% but it isn't working for this scenario. I think it is a rounding issue but could not get it to work using a modified version of the rounding formula you provided. This is the formula I have that determines the "Loans W/Perfect Scores Needed": =IF(E4>=E5,"N/A",(ROUND(ROUNDUP(E3*(E5-E4)/(1-E5),1),0)))

Loans Counted in Current Score 10

Current Score 50.00%

Target 65.00%

Loans W/Perfect Scores Needed 4

Resulting New Score 64.29%

mecerrato, You have been posting here for sometime, please use the PRE tags to align your data

and use Row Numbers and Column Letters.You posted data with no other information, I have no idea what piece goes where.

As to your formula, try using the

Evaluate Formulafunction on the ribbon.Select the cell with your formula,

On the Ribbon, select theFormulaTab

Click on theEvaluate Formulabutton

Then slowly step thru your formula and see how it is evaluated and

what numbers it shows, you should hopefully be able to discern where it is

going wrong and make a correction.MIKE

Sorry about the pre-tag miss, my apologies. I was able to resolve the issue using the ceiling on that formula and it resolved it. The data is not as decimal driven as the first formulas you help me with.

However I discovered another scenario that does not work properly due to the rounding issue and was hoping you can see what can be done to fix it. I have tried stepping through it but can't figure it out.This scenario should return 1 loan needed but it is returning 0:

Loans Counted in Current Score 6 Current Score 86.67% Target 87.50% Loans W/Perfect Scores Needed to Achieve Minimum Score 0 Resulting New Score 86.67%

I tried your last set of figures but am unable to get the formula to work. I think your problem lies in the method your using.

When you start multplying and dividing percentages, no matter

how you try, I don't believe one formula will cover all possible out comes.For one set of figures, you round the answer out two places, and it will work

but when you input a different set of figures the formula fails because it needs to

be rounded out to three digits or some other combination.I still don't understand completely how you derive your figures,

but I think some type of a Lookup Table which retuns the appropriate number

might be better suited to your needs.

But not having a complete understanding of how you derive all your figures

I'm at a loss to suggest how to set it up.Sorry.

MIKE

OK, I took all your examples, and this formula seems to work for all of them: =ROUND(ROUNDUP(B1*(B3-B2),1)/(1-B3),0)

No guarantee it will work for all combinations.

Still think a lookup table is a better solution.

MIKE

It works better in the sense that it is giving me more than I need on certain scenarios but still not right, the scenario below should result in 1 as 1 would give me a score of 87.5%, the result of 2 also works as it technically gets them a 87.56%. Loans Counted in Current Score 14 Current Score 86.67% Target 87.50% Loans W/Perfect Scores Needed to Achieve Minimum Score 2 Resulting New Score 88.34%not sure how a lookup table can work with this project as we need to calculate the number of loans needed to achieve a 87.5% based on their current score.

Let me explain how I derive my numbers:

every loan has a maximum possible points of 25 if your loan is perfect. So if you submit 2 loans and you get 50 points then your score is 100% but if those same 2 loans only earned 20 points each (because you failed certain parts of the quality test) then your score is 80% (40 points divided my the maximum points of 50). The minimum score to pass is 87.5%. if you don't have the minimum score of 87.5% I want to be able to tell you how many loans with a perfect 25 you would need to get 87.5%. So in this example you would need 2 perfect scores to get you at or above 87.5%; (if you got the 2 additional perfect loans your scores would be 20, 20, 25, 25 totaling 90 out of a possible 100 (25 X 4) points which gives you a new score of 90%Another example, if you submit 3 loans and your scores are 20, 15, and 25 that totals 60 points; the maximum possible is 75 (25 X 3) so your resulting score is 80% (60/75) . you would need 2 perfect scores to get you at or above 87.5%; in this example (if you got the 2 additional perfect loans your scores would be 20, 15, 25, 25, and 25 totaling 110 out of a possible 125 points (25 X 5) which gives you a new score of 88% (110/125)

I cannot think of how a lookup table can achieve this but am open to suggestions.

every loan has a maximum possible points of 25 if your loan is perfectSo 25 is the top score, and I assume that 0 is the bottom score,

so how is the scoring broken down?

Is it increments of 1, 2, 5?And another function to try would be the TRUNC() function.

So instead of rounding everything, perhaps simply truncating the number

will yield a better result or some combination of rounding & truncating.MIKE

Another thought about how the math is done.

if you submit 3 loans and your scores are 20, 15, and 25 that totals 60 points;Shouldn't that be 20 + 15 for a total of 35,

because 25 is a perfect score, shouldn't it be excluded?

or if not excluded shouldn't it be 0?

Including the 25's may be skewering the scores.

I'm not a mathematician, so not sure.MIKE

mecerrato I'm sorry, but I've tried all the tricks I can think of to get a consistent result,

but have not been able to find a suitable formula.So unless someone else has some ideas, I'm afraid I can only offer this link to how to build a Three Variable Data Table perhaps you will be able to modify it to suit your needs.

https://www.youtube.com/watch?v=27l...

Sorry

MIKE

Ask Your Question

Weekly Poll