excell’s formula

Question

excell’s formula

Asked 7 years ago

Viewed 68 times

2

I have three lists of scores, the first and the second vary from 5 to 10 and the third from 0 to 150. When summing up the data, obviously the variations of the third will be greater than in the first two. So I look for a formula that first determines the interval between the initial values so that after setting a ceiling of 10 and a minimum of 5, and can apply the same percentage value of the initial intervals and thus not allow a large variation in relation to the first values and not take the credit imposed by the differences of the third.

What is my purpose? - These are two lists of student test scores worth 10. - A list of work notes worth 150. I would like to add them all, giving weight of 33% for each, allowing to maintain the merit of the intervals of all of them and that the variations of the last are so relevant in relation to the insignificance of the variations of the two largest.

Is it possible?

Edit the issue to add the code you’ve already used to try to resolve the issue. Also indicate if you want a solution with VBA or through Interop with . NET/OOXML.

– Oralista de Sistemas

2017/10/25 at 12:23
Please read the Manual on how NOT to ask questions, How we should format questions and answers? and Be more specific in the question. If you can add a small example with images of how your data is at the moment and how you want the result, it would help to better understand the problem.

– danieltakeshi

2017/10/26 at 10:50
Thank you very much!

– Carlos Filgueiras

2017/10/26 at 12:58

2 answers

Browser other questions tagged excel

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

by Fernando • **1,027** points · Answer 1 · 2017-10-25T13:26:06+00:00

1

To turn the third list into the equivalent scale of the first two, you can use the following formula:

ValorLista3/30+5

Taking the table below as an example:

The formulas could be like this:

@Fernando. That’s it! The average of the two should be zero, because it was less than 5 failed. Your answer is the solution I was looking for! Thank you very much!

– Carlos Filgueiras

2017/10/26 at 14:21
@Carlosfilgueiras How good it worked, could mark Fernando’s answer in response?

– danieltakeshi

2017/10/26 at 15:42
@Carlosfilgueiras, good that helped. You could mark the answer as accepted?

– Fernando

2017/10/31 at 11:24

by danieltakeshi • **3,960** points · Answer 2 · 2017-10-26T11:28:09+00:00

If I understand correctly, you want to know in percentage the note according to the range (span).

This is widely used in instrumentation, where there are several different values, with different units, so values are converted to percentage and then calculations are performed.

All are transformed into a percentage with a rule of 3. For example, the range is 5 - 10, so with a note of 7.5, you have 50%.

So you can make this rule formula of 3 for the three columns, add them up and then divide by 3. So you have the arithmetic mean in percentage of the 3 notes.

For example:

Where the data is in columns E, F and G and row 3. Then the average in percentage is given by the formula =((((E3-2)/(5-2))+((F3-5)/(10-5))+((G3-0)/(150-0)))/3)*100

Note that this formula will work only if each range has the same weight.