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

#### paulwebb

##### Board Regular
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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### RickXL

##### MrExcel MVP
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:

Code:
``````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

##### Board Regular
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:

Code:
``````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

Replies
0
Views
238
Replies
9
Views
486
Replies
6
Views
473
Replies
4
Views
332
Replies
1
Views
891

1,195,582
Messages
6,010,577
Members
441,557
Latest member
Jbest23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back