Solved

# excel macro to do math formula

Posted on 2013-02-06

I need excel macro to implement the flowing formula

X=x-min(Xi) / max (x) - min (x)

Say we have following Columns and rows

A B C .... AO.

1- 20 30 70

2- 12 6 43

3- 25 92 67

4- 36 45 28

5- 22 29 34 ....

X=x-min(Xi) / max (x) - min (x)

Then the formula starting on Col A for A1 = 20 would be as follows:

MIN of all values in Col A = 12

MAX of all values in Col A = 36

So the A1 that is 20 would be (A1-12)/(36-12) = 8/24 = 0.3333

and with the same logic for A2 and A3 and A4 ……etc it would be

(A2-12)/(36-12) = 24/86 = 0.2791

etc ... for A3, A4, A5 till the end of column A, I usually use hold and drag the output of A1, which will automatically give me the output of A2, A3, A4,….. etc like this

20 0.333333

12 0

25 0.541667

36 1

22 0.416667

Then for column B

MIN of all values in Col A = 6

MAX of all values in Col A = 92

So: X=x-min(Xi) / max (x) - min (x)

= (B1-6)/(92-6)= 0.27907 and here I will hold and drag the output down it will automatically give me the B2, B3, B4, B5….etc

30 0.27907

6 0

92 1

45 0.453488

29 0.267442

Please tell me if it is not clear. Thanks