CODEWITHSUNDEEP
excelvba
Ziezi
Ziezi

Reputation: 6467

Rescalling a range of numbers depending on the interval they belong to

I have the following conversion table:

enter image description here

and I need to convert the values of the second column to the values in the first column, as continuous variables.

Currently, I am trying to write a function that accepts a value from the second column, check its validity and then uses the following conversion formula for the data transformation:

enter image description here

The code currently looks like this:

Public Function ReScale (input As Double)

  'Max and Min values of the old scale.
   Dim MinOld As Double: MinOld = 2
   Dim MaxOld As Double: MaxOld = 6

  'Test input value validity.
   If input > MaxOld Or input < MinOld Then
     ReScale = CVErr(xlErrNA)
   End

   Dim MinNew As Double
   Dim MaxNew As Double

   'Find in which interval the input value belongs.
   If input >= 5.5 Then
     MinOld = 5.5
     MaxOld = 6.0
     MinNew = 85
     MaxNew = 100
   ElseIf input >= 4.50 And input <= 5.49 Then
     MinOld = 4.50
     MaxOld = 5.49
     MinNew = 65
     MaxNew = 84
   ...` <--------------------- Question?

  'Transform the old data to the new scale.
   ReScale = ( (MaxNew - MinNew) / (MaxOld - MinOld) ) * (input - MaxOld) + MaxNew

End Function

My question is:

Should I search for MaxNew, MinNew, MaxOld and MinOld depending on the variable value or should I use the absolute minimum and maximum?

Is there an easier way of doing this?

Upvotes: 0

Views: 106

Answers (1)

Chronocidal
Chronocidal

Reputation: 7951

This is a bit of a Frame Challenge:

At the moment, this grading system gives a really sharp change in the grading structure at the boundaries. For example, take a look at the following conversions:
from 3.2 input to 3.3 input is an output change of 1.84, from 3.3 input to 3.4 input is an output change of 1.84, from 3.4 input to 3.5 input is an output change of 2.65, from 3.5 input to 3.6 input is an output change of 1.92, from 3.6 input to 3.7 input is an output change of 1.92

A Grading Curve is generally called that for a reason - to give a smooth distribution and transition. If you were to drop a cubic trendline on your graph, you can almost (but not quite) get there with y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277:
Graph showing trendline and converted values
(I have circled the sharp transitions, and the fact that the equation given currently goes negative at x=2)

Upvotes: 1

Related Questions