This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

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

The formula for A20 is...

=(A1-MIN(A$1:A$5))/(MAX(A$

... Drag & Fill or Copy & Paste as required.

Please note that I've not included code to avoid Divide by Zero errors. Would you like something for that?

Regards,

Brian.

.

Install in here your data file and activate the button Formula I warn you it will take al lot of time as you hv lots of data. I tried by all means to have formula being populated automatically but without success.

Let it run for some 1000+ records and check the results in Col A if it is ok the just press again on the F5 in the code and it will continue running it will go thru each Column till it hit all the columns except the last 2.

To stop it when it is processing press on CTRL and BREAK at the same time.

gowflow

ApplyFormulaNoData.xlsm

Dear gowflow , thanks for the code. but it is not handy as really it is very slow.

I am thinking of another approach, which will the formula works very fast, let me check it and i will be back soon.

If you're using Excel 2007/2010/2013....

...or, to support earlier releases,...

Are these formulas not fast enough? How many columns do you have? Are there other formulas? Conditional Formatting? Which version of Excel? How old is the PC?

Regards,

Brian.

I have excel 2010 64bit installed on windows 7 pro 64 bit.

I have two data set one with 494021 records and the other with 311029 records.

Apologies, but it's not obvious how you want the formula modified for the extra rows. Currently it's taking a block of 5 rows and analysing them over five rows, 20 rows down. Perhaps you could give me one column - it's the formulas I want, so by all means clear the data cells.

Past my bedtime, so I'll be back to this in the morning.

Thanks,

Brian,

That link ends up trying to download an executable, which I'm not willing to run. Probably no great loss as I'm running 32bit Windows and Excel. If I had to process this data, I'd probably do it a chunk at a time, immediately replacing each chunk's formulas by their values. I'd also store the results in a separate file - that way I could analyse the results without having to drag the original data along. (This also gives you the possibility of merging the two datasets.)

Regards,

Brian.

Yes I know I tried so loong to get it to work by formula but did not succeed so this is why I need from you the following:

Tell me about hte formula and how you do it:

You put in A2 this ?

=(A2-Min("A2:A496202"))/(M

and drag it down ?

When you do this manually I mean I need the formula that you put when you do it manually.

As if it is like this then you are generating a Circular Reference error as the cell refrence itself.

Pls let me know.

gowflow

DATASET ACTIVATE DataSet1.

COMPUTE NV1 = ( V1 - 0 ) / ( 58329 - 0 ) .

COMPUTE NV2 = ( V2 - 1 ) / ( 3 - 1 ) .

COMPUTE NV3 = ( V3 - 1 ) / ( 66 - 1 ) .

COMPUTE NV4 = ( V4 - 1 ) / ( 11 - 1 ) .

COMPUTE NV5 = ( V5 - 0 ) / ( 693375640 - 0 ) .

COMPUTE NV6 = ( V6 - 0 ) / ( 5155468 - 0 ) .

COMPUTE NV7 = ( V7 - 0 ) / ( 1 - 0 ) .

COMPUTE NV8 = ( V8 - 0 ) / ( 3 - 0 ) .

COMPUTE NV9 = ( V9 - 0 ) / ( 3 - 0 ) .

COMPUTE NV10 = ( V10 - 0 ) / ( 30 - 0 ) .

COMPUTE NV11 = ( V11 - 0 ) / ( 5 - 0 ) .

COMPUTE NV12 = ( V12 - 0 ) / ( 1 - 0 ) .

COMPUTE NV13 = ( V13 - 0 ) / ( 884 - 0 ) .

COMPUTE NV14 = ( V14 - 0 ) / ( 1 - 0 ) .

COMPUTE NV15 = ( V15 - 0 ) / ( 2 - 0 ) .

COMPUTE NV16 = ( V16 - 0 ) / ( 993 - 0 ) .

COMPUTE NV17 = ( V17 - 0 ) / ( 28 - 0 ) .

COMPUTE NV18 = ( V18 - 0 ) / ( 2 - 0 ) .

COMPUTE NV19 = ( V19 - 0 ) / ( 8 - 0 ) .

COMPUTE NV20 = ( V20 - 0 ) / ( 0 - 0 ) .

COMPUTE NV21 = ( V21 - 0 ) / ( 0 - 0 ) .

COMPUTE NV22 = ( V22 - 0 ) / ( 1 - 0 ) .

COMPUTE NV23 = ( V23 - 0 ) / ( 511 - 0 ) .

COMPUTE NV24 = ( V24 - 0 ) / ( 511 - 0 ) .

COMPUTE NV25 = ( V25 - 0 ) / ( 1 - 0 ) .

COMPUTE NV26 = ( V26 - 0 ) / ( 1 - 0 ) .

COMPUTE NV27 = ( V27 - 0 ) / ( 1 - 0 ) .

COMPUTE NV28 = ( V28 - 0 ) / ( 1 - 0 ) .

COMPUTE NV29 = ( V29 - 0 ) / ( 1 - 0 ) .

COMPUTE NV30 = ( V30 - 0 ) / ( 1 - 0 ) .

COMPUTE NV31 = ( V31 - 0 ) / ( 1 - 0 ) .

COMPUTE NV32 = ( V32 - 0 ) / ( 255 - 0 ) .

COMPUTE NV33 = ( V33 - 0 ) / ( 255 - 0 ) .

COMPUTE NV34 = ( V34 - 0 ) / ( 1 - 0 ) .

COMPUTE NV35 = ( V35 - 0 ) / ( 1 - 0 ) .

COMPUTE NV36 = ( V36 - 0 ) / ( 1 - 0 ) .

COMPUTE NV37 = ( V37 - 0 ) / ( 1 - 0 ) .

COMPUTE NV38 = ( V38 - 0 ) / ( 1 - 0 ) .

COMPUTE NV39 = ( V39 - 0 ) / ( 1 - 0 ) .

COMPUTE NV40 = ( V40 - 0 ) / ( 1 - 0 ) .

COMPUTE NV41 = ( V41 - 0 ) / ( 1 - 0 ) .

EXECUTE.

Let me see what I can come up with

gowflow

It looks diffrent now

is this how you do it ? Row1 all columns then row2 all columns etc .... ?

gowflow

A2 - MIN(Xi)/MAX(Xi)-MIN(Xi) ?? Maybe it is a preset function in Excel ??

gowflow

Now that we're no longer dealing with 5 rows of data, it's no longer clear what the formula is. What should the formula in A494022 be?

Thanks,

Brian.

Hv tried to get the values instead of formula ... no success all turns to zeroes.

The best I had acheive is what I posted earlier is a turtle type of walk slow ... but sure will get there after 24 hours .!!!!

Good luck if you find a better way, I spend till 4 AM ystday on this with no luck ! My last question was if possibly this is a built in formula in Excel then it should be no sweat. Not too familiar with these probability/financial functions will wait for reoroman reply to my last post.

gowflow

OK, I tried out the ideas I mentioned earlier. Please do the following...

- Create a new file and add the code below to it. Edit the Chunk_III macro to reflect your source book, sheet and range.

- Open your source book.

- Run the Chunk_III macro.

The macro adds a sheet and, column by column, writes the formulas and replaces them by their values. Even with 32bit Windows and Excel, this works fine (with a slight workaround), so it should be a walk in the park for reoroman's PC. (For 500,000 rows, my PC takes 7 or 8 seconds for each of the 39 columns.)

```
Option Explicit
Sub Chunk_III()
Dim i As Long
Dim xRange As Range
Dim xSheet_List As String
Dim xRange_List As String
Dim xBook_List As String
Dim xSource As Worksheet
xBook_List = "Lots_of_Columns.xlsb"
xSheet_List = "Sheet1"
xRange_List = "$A$2:$A$500001"
If Not Book_Exists(xBook_List) Then
MsgBox ("Please open """ & xBook_List & """ and retry - run cancelled.")
Exit Sub
End If
If Not Sheet_Exists(xSheet_List, xBook_List) Then
MsgBox ("Sheet """ & xSheet_List & """ not found in """ & xBook_List & """ - run cancelled.")
Exit Sub
End If
If Range(xRange_List).Rows.Count Mod 5 <> 0 Then
MsgBox ("The source range (" & xRange_List & ") must be a multiple of 5 rows - run cancelled.")
Exit Sub
End If
Set xSource = Sheets.Add
xSheet_List = "[" & xBook_List & "]" & xSheet_List & "!"
Range("A2") = "=IFERROR((" & xSheet_List & "A1-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Range("A3") = "=IFERROR((" & xSheet_List & "A2-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Range("A4") = "=IFERROR((" & xSheet_List & "A3-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Range("A5") = "=IFERROR((" & xSheet_List & "A4-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Range("A6") = "=IFERROR((" & xSheet_List & "A5-MIN(" & xSheet_List & "A1:A5))/(MAX(" & xSheet_List & "A1:A5)-MIN(" & xSheet_List & "A1:A5)),"""")"
Sheets.Add
Application.ScreenUpdating = False
For i = 0 To 38
Debug.Print i & " - " & Now()
xSource.Range("A2:A6").Copy Destination:=Range(xRange_List).Offset(0, i)
Range(xRange_List).Offset(0, i).Formula = Range(xRange_List).Offset(0, i).Value
Next
Application.DisplayAlerts = False
xSource.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Function Book_Exists(xBook_Name As String) As Boolean
Book_Exists = False
On Error Resume Next
Book_Exists = (Workbooks(xBook_Name).Name = xBook_Name)
On Error GoTo 0
End Function
Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean
If xBook = "" Then xBook = ActiveWorkbook.Name
Sheet_Exists = False
On Error Resume Next
Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next
End Function
```

Regards,Brian.

Quotes 1 [gowflow]: Well I looked again at your dataset that you posted and this is not how you explained it at first !! You are coing each row all the columns where before you mentioned you do a Col A all rows then Col B all rows ....

It looks diffrent now

is this how you do it ? Row1 all columns then row2 all columns etc .... ?

gowflow

Answer: No, Row1 all columns then row2 all columns etc... , is not the way that formula does.

This should work on the columns now rows.

SPSS has columns from v1 to v41, and the bellow command will create beside those columns other columns from nv1 to nv41 by implementing the following formula:

Computer nv1 = (v1-(min value between data from 1 to 494021) which is 0.) / (The maximum value between data from 1 to 494021) which is 58329) - (min value between data from 1 to 494021) which is 0.)

so the minimum is 0 and the maximum is 58329 is static in V1 and it will be implemented in all data from 1 to 494021 on the V1 and will be saved in new column named NV1 see …

COMPUTE NV1 = ( V1 - 0 ) / ( 58329 - 0 )

Quotes 2 [gowflow]: One mor question what is the name of this function

A2 - MIN(Xi)/MAX(Xi)-MIN(Xi) ?? Maybe it is a preset function in Excel ??

gowflow

I do not think the formula is preseted in excel. It is used for min-max normalization is as equation:

Xn = (X0 – Xmin) / (Xmax – Xmin)

Where,

Xn = new value for variable X

X0 = current value for variable X

Xmin = minimum value in data set

Xmax = maximum value in data set

Please post the name of the file, the sheet and the range containing the data.

Thanks,

Brian.

here is the data http://www74.zippyshare.com/v/46259397/file.html

data range is from A to AO column

the formula should be for 494021 records in every column from A to AO, did i got you right?

(1) What is the extension of the file?

(2) As I mentioned, your link requires downloading and running an exe, which I'm not willing to do.

(3) Again, I need to know what formula you want! The one already provided worked a range of 5 - is that what you want for the 500,000 rows? Ideally, I'd like you to post the first column of the formulas, but if you at least gave me a half-dozen rows that might help.

Brian.

I do not know where you click on the previous link so you got to download an executable file.

check the attached picture where you should click, as i tried it and it works fine.. any way the file .csv and i put it in rar as it is big file.

zip.JPG

(

quote [Again, I need to know what formula you want! The one already provided worked a range of 5 - is that what you want for the 500,000 rows?]

I wrote that .. it is Xn = (X0 – Xmin) / (Xmax – Xmin)

Where,

Xn = new value for variable X

X0 = current value for variable X

Xmin = minimum value in data set

Xmax = maximum value in data set

The name of the file is "train.csv", the name of the sheet is "train" and the range is "$A$1:$AO$494021".

Please post the formulas you'd expect to see in A494022 and A494023. Is that a problem?

Brian.

I'm faitly certain that the formulas in the attached are wrong - the only thing going for them is that they produced the same answers as the examples in your original post. (A side effect is that the total no. of rows must be a multiple of 5 so the last row is ignored.)

Something I didn't make clear is that the range specified should just be for column A - the code assumes that the last column is AO.

There are a lot of blank cells in the output - Divide by Zero errors.

Finally, just open your csv file, open this file and press the blue button.

Brian.Chunk-III.xlsb

example:

A494020 which its value is 0 would be after formula like this: =(A494020-0)/(58329-0) = 0

and

A494021 which its value is 0 would be =(A494021-0)/(58329-0) = 0

check the outcome of column A only after applying the formula on it. answer in column b.

https://hotfile.com/dl/193193971/88dece0/column_A_only.csv.html

please make format cell decimal numbers = 4

Crossing posts, please see my previous one.

As I suspected, the formula is different. I'll try a few things and get back to you.

Brian.

I am still not clear for your formula

you said:

```
Xn = (X0 – Xmin) / (Xmax – Xmin)
Where,
Xn = new value for variable X
X0 = current value for variable X
Xmin = minimum value in data set
Xmax = maximum value in data set
```

Now to go back to the data you posted say we take Col A and you have following values:

and suppose we only have 8 rows but in your case it would go to the maximum rows you have say 496000+

Col A

Row 2 20

3 0

4 60

5 25

6 1

7 18

8 0

When we want to convert A2 it is = 20 - Min(Row 2 to Row 8) / Max(Row 2 to Row 8) - Min(Row2 to Row 8)

A2 = 20 - 0 / 60 - 0 = 20 / 60 = 0.3333

so after running A2 we have now

Col A

Row 2 0.3333

3 0

4 60

5 25

6 1

7 18

8 0

Again A3 = 0 - 0 / 60 - 0 = 0

A4 = 25 - 0 / 60 - 0 = 0.4167

and the 8 row would be

Col A

Row 2 0.3333

3 0

4 1

5 0.4167

6 0.0167

7 0.3

8 0

Is that correct ???

gowflow

no that is not right,,as the maximum and minimum always static and in your case it is

60 maximum

and 0 minimum

Col A

Row 2 20

3 0

4 60

5 25

6 1

7 18

8 0

so the answer should be in col B for example and that would be

col B

0.333333333

0

1

0.416666667

0.016666667

0.3

0

Oh, that's new? As I explained, my macro puts it into a new sheet. Once you're happy with the content, putting it elsewhere is easy.

The file in this post is gold.

Regards,

Brian.

Couple of things...

(1) It may be a 32bit thing, but it's probably a good idea to recycle Excel before starting.

(2) On my PC, at least, it appears that one big Max and Min from VBA is appreciably slower than 500,000 smaller ones from formulas. I may be able to fix that by simply moving the Max and Min's to the sheet.

Brian.

Something's the matter with your PC - on my old, 32bit s/w PC it's taking between 3 and forty seconds per column.

Any ideas?

Brian.

If suddenly u hv zeroes there it will be trumendously slow.

gowflow

Just noticed you closed the question ! tks v much but feel here the credit goes to redmondb he gave you a good fast solution !!!!

Shows ur a gentlman !!! not like an other question I spend most of overnite on it .... to get a nice donut !!! not that am after points or anything but it tell u much about people and atitude wow !

Anyway most important is to hv fun !

cheers

gowflow

Sorry, I don't understand - the only blanks in IV are from an error. The error would almost certainly be a Divide by Zero - and that would only happen when all the values in a column were the same.

Sure you're using IV?

Regards,

Brian.

Sorry, more crossing posts.

I got to share points with you, we got an A Grade and I got the "Accepted Solution". :)

Life is good!

Regards,

Brian.

Edit: Oops - thanks, reoroman.

Thanks. If you're comfortable with the VBE, the macro outputs timings into the Immediate Pane before processing each column.

Brian.

but till now 7 minutes has passed and no results.

maybe i have something wrong with my desktop. I will try it later on my laptop

Updating the screen slows down Excel, so lots of big runs (including this one) tuen off screen updates until the run is finished. If you're happy with the results and will be running this job repeatedly then let me know and I'll show you how to see progress.

I'm sfraid not, but I'm sure you'll find lots of experts who are!

Brian.

If it's still running then something's interfering. Do you want to troubleshoot this or will you simply wander off into the sunset with SPSS?! :)

Brian.

Your loss! But again, why did you bother with Excel in the first place?

Brian.

gowflow

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Please try the attached. Just open your Train.csv, open this and click on the blue button.

Brian.Chunk-IV.xlsb