Excel: create scale with 2 known points
Wave,
I'm having a problem-q I believe to be much more mathematical than a formula in Excel.
I have a list with percentages of results and I need to "scale" these values according to two known points.
THE MINIMUM VALUE = 25 POINTS
THE MAXIMUM VALUE = 99 POINTS
And ai all values between need to be staggered according to these two references.
I know that if it was just the maximum value (or only the minimum) - a rule of three would be enough to stagger everything.
Ex:
MAXIMUM VALUE = 54 % = 99 POINTS.
Then to 50% - a rule of three - 91.67 points.
Being the formula:
X = (99 x 50) / 54
My doubt is how to get the scale if we have TB the minimum value..
For example
MAXIMUM VALUE = 54%.... 99 points
MINIMUM VALUE = 12%.... 25 points
How many points are 42% worth? 23%?
I swear I am burning the Cuckoo.. kkkkk
Thank you Daniel
2 answers
So, you have two scales:
Points:
25 Pontos - 99 Pontos, sendo seu centro 66 Pontos e seu range 74 Pontos (99 - 25 = 74)
Percentage:
12% - 54 %, sendo seu centro 33% e seu range 42% (54 - 12 = 42)
Now think about how much would 25% be on the point scale? We can't just divide 62 (50%) by 2, the correct would be to take 62 - 25 = 37 and divide 37 / 2 - 18,5 and putting it on the scale would be:
25 + 18,5 = 43,5.
So far so good.
How much is 25% on the scale of Points
Let's use the range of points to simplify the calculations in Rule 3.
(74 * 25) / 100 = 18,5 - Rule of 3 between 74 (range) 25 (porcentagem)
This 18,5 represents when it must be added from the 25 points to reach 25% within the scale, i.e. 43.5 points.
Calculating other values
Quanto 45% na escala de porcentagem reflete na de pontos.
First you convert 45% within your scale, in the Range:
Makes the rule of 3:
(33 * 100) / 42 = 78,57
Having How many % equals within your range, you now transfer the % to the other Range:
(74 * 78,57) / 100 = 58,14
As said above, you add this to the minimum value to hit within the scale. No case 25 Pontos + 58,14 Pontos = 83,14 Pontos
I think that's it. :)
I couldn't quite understand your question, but from the looks of it, perhaps a plausible solution is to bring your values first to 0. Its minimum value is currently 25, if 25 is the minimum I understand it would be the 0%. Its maximum value currently is 99, if 99 is the maximum I understand it would be the 100%. To facilitate the accounts you could set the minimum value to 0, soon it did: minimum - 25 = 0 maximum - 25 = 74
Subtract 25 from all values calculate the percentages, at the end you can do the reverse calculation by adding 25.