Macro to scale event data ..... help please

[paulwebb]

Hi all

I've been trying to write a macro which will scale various data classes for different events, but I'm an amateur at vba, and after about 5 hours messing about I'm nearly ready to throw the laptop, lol.

I know it is fairly simple, I'm just not getting it.

Data in columns B and C identify the event (number of rows varies)

Data in columns E - P has numeric details of specifics relating to that event. The numeric data can be negative as well as positive.

What I need to do is to scale columns E - P for each event, in the range 0 - 1, where 0 represents the lowest value and 1 the highest value.

I've managed through various attempts to learn what doesn't work and have finally decided to ask for help, before my frustrations boil over totally.

Any assistance greatly appreciated

Thank you

Paul

 

[RickXL]

Hi,

You can do this longhand with a formula but you could always use the Forecast() worksheet function.

This is not meant to be final code but just to give you the idea. Forecast() is like using a straight line graph to do a data look up. You give it the max and min x and y values and it will do the conversion for you.

In your case you already know the min and max values for y (0 and 1).
You will need to use Min() and Max() to find out the min and max y values.
Then you put them all into an array and insert into the Forecast() function:

Sub xx()

    Dim wsf As WorksheetFunction
    Set wsf = Application.WorksheetFunction
    
    xs = Array(wsf.Min(Range("A2:A20")), wsf.Max(Range("A2:A20")))
    ys = Array(0, 1)
    x = -5
    y = WorksheetFunction.Forecast(x, ys, xs)
    
    Debug.Print y

End Sub

(I set up wsf because I don't like entering the long form everywhere.)

Hopefully, another laptop remains safe.

 

[paulwebb]

Hi,

You can do this longhand with a formula but you could always use the Forecast() worksheet function.

This is not meant to be final code but just to give you the idea. Forecast() is like using a straight line graph to do a data look up. You give it the max and min x and y values and it will do the conversion for you.

In your case you already know the min and max values for y (0 and 1).
You will need to use Min() and Max() to find out the min and max y values.
Then you put them all into an array and insert into the Forecast() function:

Sub xx()

    Dim wsf As WorksheetFunction
    Set wsf = Application.WorksheetFunction
    
    xs = Array(wsf.Min(Range("A2:A20")), wsf.Max(Range("A2:A20")))
    ys = Array(0, 1)
    x = -5
    y = WorksheetFunction.Forecast(x, ys, xs)
    
    Debug.Print y

End Sub

(I set up wsf because I don't like entering the long form everywhere.)

Hopefully, another laptop remains safe.

That's great Rick, gives me something to work from, thank you for your time.......laptop is safe for now