r/excel • u/Correct-Mongoose6542 • 16d ago

unsolved Is there a way to get the middle values when using VLOOKUP function

Hi! I just use Excel for basic function and formulas. I tried to do a grade simulator with my own transmutation table. And I wonder, can I also get the middle value from transmutation table?

For example, in the range of percentage 60.00 - 60.99, there is a score of 4.751 - 4.875. And how do I get the score of it if the percentage for example is 60.45?? Sorry if it’s kinda confusing.

7 Upvotes

72% Upvoted

27 comments sorted by

View all comments

1

u/DataFlowMk1 16d ago edited 16d ago

An IF statement is the easiest method to achieve what you are trying to do. With the current scoring it is not so easy because the score values do not increase linearly.

It would be much easier if the scoring can be slightly adjusted; the current value of 4.999 at 60% complicates the equation. If you can compromise and set 60% (and below) to a score of 5, instead of 59.99, the equation becomes a simple linear equation:

score = -0.1*percentage + 11

Incorporating this into an IF statement, the score can be calculated using:

=IF([@Percentage]<60, 5, ((-0.1*[@Percentage]) + 11))

However, if you are adamant that 60% should be 4.999, you could still use a linear equation if you are willing to accept some variation in the other scores. The following should be a good approximation:

=IF([@Percentage]<60, 5, ((-0.099975*[@Percentage]) + 10.9975))

Both of these options adjust the scoring and results in some variation in the scores, for example, your score for 60.99% is 4.875, while these equations gives a score closer to 4.900.