Excel: Create scale with 2 known points

Asked

Viewed 691 times

2

Hello,

I have a problem - q I believe to be much more mathematical than a formula in Excel.

I have a list with percentages of results and need to "scale" these values according to two known points.

MINIMUM VALUE = 25 POINTS

MAXIMUM VALUE = 99 POINTS

and then all values between need to be staggered according to these two references.

I know that if it was only the Maximo value (or only the minimum) - a rule of three would be enough to scale everything.

ex:

MAXIMUM VALUE = 54% = 99 POINTS.

then for 50% - in a rule of three - 91,67 points.

being the formula: 

X = (99 x 50) / 54

my doubt is how to achieve scale if we have tb the MINIMUM VALUE..

for example

MAXIMUM VALUE = 54% .... 99 POINTS

MINIMUM VALUE = 12% .... 25 POINTS

How many points are worth 42%? 23%?

I swear I’m burning the cuckoo.. kkkkk

thank you Daniel

excel calculus

  • Your question is confused, does 54% amount to 99 points? Set the context in which it fits and we can help you better :)

  • 1

    hi @Leandrofelipemoreira first, thanks for your help! the doubt is simple (the solution must be for those who understand mathematics) - I have a fixed scale of points ranging from 25 to 99 points. And I have a leverage index that ranges from 12 percent to 54 percent - that is - the lowest take-all on the list is 12 percent - the highest 54 percent. I need to match the two scales - for this I have these 2 comparison points - ie - 12% which is the minimum will receive on the point scale - 25 points q is the minimum there. Ja 54% that is the maximum take - receives 99 points.

  • 1

    There I need to convert all the values that are between 12-54% on the scale to receive the respective points (between 25-99 points). For example, at first I already know that 33% will amount to 62 points (pq is the MEDIAN value of each scale) - (54+12 = 66/2 = 33%) and (99+25 = 124/2 = 62 points). But for a 45% take - for example - qtos points I must assign proportionally in the point scale?

  • I answered down there, I didn’t put it in the formula, but I showed you how the calculus works.

2 answers

2

So you have two scales:

Dots:

25 Pontos - 99 Pontos, sendo seu centro 66 Pontos e seu range 74 Pontos (99 - 25 = 74) inserir a descrição da imagem aqui

Percentage:

12% - 54 %, sendo seu centro 33% e seu range 42% (54 - 12 = 42) inserir a descrição da imagem aqui

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 put on the scale would be:

25 + 18,5 = 43,5.

So far so good.

How much is 25% in the Points scale

We will use the Dots Range to simplify the 3 rule calculations.

(74 * 25) / 100 = 18,5 - Rule of 3 between 74 (range) 25 (porcentagem)

That one 18,5 represents when it must be added from the 25 Points to reach 25% within the scale, ie 43.5 Points.inserir a descrição da imagem aqui

Calculating other values

Quanto 45% na escala de porcentagem reflete na de pontos.

First you convert 45% within your range, into the Range:

inserir a descrição da imagem aqui

Make the rule of three:

(33 * 100) / 42 = 78,57

Having how much % equals within your range, you now transfer the % to the other range:

(74 * 78,57) / 100 = 58,14

As said up there, you add that up to the minimum value to hit within the scale. In this case 25 Pontos + 58,14 Pontos = 83,14 Pontos

inserir a descrição da imagem aqui

I guess that’s it. :)

0

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 currently is 25, if 25 is the minimum understand that would be 0%. Its maximum value currently is 99, if 99 is the maximum I understand that would be 100%. To facilitate the accounts you could set the minimum value as 0, so did: minimum - 25 = 0 maximum - 25 = 74

Subtract 25 of all values calculate the percentages, in the end you can do the inverse calculation by adding 25.

Browser other questions tagged excel calculus

You are not signed in. Login or sign up in order to post.