Formula help to calculate scores and units

February 15, 2017 at 14:37:31
Specs: Windows 7
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


See More: Formula help to calculate scores and units

Report •

#1
February 15, 2017 at 19:24:50
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.


Report •

#2
February 15, 2017 at 20:06:50
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 cells

issue #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)


Report •

#3
February 15, 2017 at 20:23:09
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)

Report •

Related Solutions

#4
February 16, 2017 at 10:57:44
In your second formula, the use of the CEILING() function may be your problem.

The ceiling function rounds a number up or 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 8

You might want to try one of the other rounding functions and see how they work.

MIKE

http://www.skeptic.com/


Report •

#5
February 16, 2017 at 11:58:58
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

http://www.skeptic.com/


Report •

#6
February 16, 2017 at 13:50:15
Thank you mmcconaghy , you are always reliable :-)

Report •

#7
February 16, 2017 at 20:01:07
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%


Report •

#8
February 17, 2017 at 06:59:39
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 Formula function on the ribbon.

Select the cell with your formula,
On the Ribbon, select the Formula Tab
Click on the Evaluate Formula button
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

http://www.skeptic.com/


Report •

#9
February 17, 2017 at 11:56:37
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%


Report •

#10
February 17, 2017 at 13:28:08
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

http://www.skeptic.com/


Report •

#11
February 17, 2017 at 17:44:51
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

http://www.skeptic.com/


Report •

#12
February 21, 2017 at 18:34:16
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.


Report •

#13
February 21, 2017 at 19:51:35
every loan has a maximum possible points of 25 if your loan is perfect

So 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

http://www.skeptic.com/


Report •

#14
February 22, 2017 at 04:55:07
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

http://www.skeptic.com/


Report •

#15
February 22, 2017 at 09:34:06
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

http://www.skeptic.com/


Report •

Ask Question