Solved

excel macro to do math formula

Posted on 2013-02-06
76
589 Views
Last Modified: 2013-04-24
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
0
Comment
Question by:reoroman
  • 34
  • 27
  • 15
76 Comments
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, reoroman.

The formula for A20 is...
=(A1-MIN(A$1:A$5))/(MAX(A$1:A$5)-MIN(A$1:A$5))
... 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.
.
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 250 total points
Comment Utility
Is that what you want ?
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
0
 

Author Comment

by:reoroman
Comment Utility
Dear Brian, yes, please i need the code to avoid the divide by zero error.

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.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

If you're using Excel 2007/2010/2013....
=IFERROR((A1-MIN(A$1:A$5))/(MAX(A$1:A$5)-MIN(A$1:A$5)),"")
...or, to support earlier releases,...
=IF(MAX(A$1:A$5)-MIN(A$1:A$5)=0,"",IFERROR((A1-MIN(A$1:A$5))/(MAX(A$1:A$5)-MIN(A$1:A$5)),""))

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?

Edit: I tested the second (slower) formula against 16,384 columns (and 5 rows) - as near as I could tell, the paste and calculation were instantaneous. My PC is a two year-old business laptop.

Regards,
Brian.
0
 

Author Comment

by:reoroman
Comment Utility
MY is PC is new, PC is desktop (HP) Core i7 with 3.4GHZ and 8GB RAM.

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.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, reoroman.

500,000 rows - how many columns? Any other formulas?

Thanks,
Brian.
0
 

Author Comment

by:reoroman
Comment Utility
sorry i forgot about that.. they are 41 columns..
0
 

Author Comment

by:reoroman
Comment Utility
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, reoroman.

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,
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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.
0
 

Author Comment

by:reoroman
Comment Utility
dear gowflow. for column A only, the code has only process 67959 out of 494021 in 15 minutes before i stop it. that means it needs whole day for the all 41 columns.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Hi we had time lag here due to time zones.


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"))/(Max("A2:A496202")-Min("A2:A496202"))
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
0
 

Author Comment

by:reoroman
Comment Utility
I do it like this in SPSS. First i make SPSS calculate the maximum and minimum values, then i copy the results to this SPSS syntax and run it. it will do the command fast, but i have to deal with divide on zero error after that. here is the command.


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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I thought u put the formula in excel and drag it down !!! ic
Let me see what I can come up with
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
redmondb there are 1/2 a million row with 41 columns and ranges evaluation on the entire column MAX and MIN .... When I put the formula my PC SCREAMMSSSS WOOOOOOWWWW I am hurt in pain it dies like mad.

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
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Folks,

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.)

Edit: Gowflow, pending an answer from reoroman, I'm still using the original formulas. BTW, if Min and Max should be using the entire range then my approach would be even faster - I'd only need to do 39 of each!

Edit2: FWIW, my output file is a 149MB xlsb - or a 210MB xlsm.
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

Open in new window

Regards,
Brian.
0
 

Author Comment

by:reoroman
Comment Utility
Dear Brian, could you please send me the excel sheet ready with your code, so i can implemented directly. i made mistakes when i do as you mentioned above. thanks
0
 

Author Comment

by:reoroman
Comment Utility
Hello Gentlemen, Sorry I was sleeping
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
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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


Edit: And please see earlier posts about exactly what the formula should be for 500,000 records v. 5. If my request isn't clear, please let me know.

Thanks,
Brian.
0
 

Author Comment

by:reoroman
Comment Utility
The name of the file is: train
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?
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

(1) What is the extension of the file? Edit: And the sheet name?
(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.
0
 

Author Comment

by:reoroman
Comment Utility
here is new link on hotfile: https://hotfile.com/dl/193185315/104f1ec/train.rar.html

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
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, reoroman, but I still need answers to my questions. Are they unclear?

(Edit: This has to run on your PC - I'm not uploading a 150MB file!)
0
 

Author Comment

by:reoroman
Comment Utility
what do you mean by the sheet name? i name it train

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
0
 

Author Comment

by:reoroman
Comment Utility
please upload the new formula with your code here as gowflow did, and i will attach the data to it
0
 

Author Comment

by:reoroman
Comment Utility
you do not need to upload any 150MB files, i sent you the data so you can check it as you want.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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?

Edit: Apologies, I just saw your previous post. What checking can I do with your data? The file gave me the file and sheet names plus the range, but what else does it give me?

Brian.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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
0
 

Author Comment

by:reoroman
Comment Utility
I should expect answers for all records in column A in new records say for example name AR.

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
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
reorman,

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 

Open in new window


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
0
 

Author Comment

by:reoroman
Comment Utility
welcome back gowflow..

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

Author Comment

by:reoroman
Comment Utility
so the answer should not be on the same column.. say you have

                 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
0
 

Author Comment

by:reoroman
Comment Utility
like this:
A       B
20      0.333333333
0      0
60      1
25      0.416666667
1      0.016666667
18      0.3
0      0
0
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
Comment Utility
reoroman,

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

Brian.Chunk-IV.xlsb
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:reoroman
Comment Utility
so if we have column from A to AO the answer should be placed on AR to CF
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
roroman,

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.

Gowflow,
The file in this post is gold.

Regards,
Brian.
0
 

Author Comment

by:reoroman
Comment Utility
yes redmondb it not an issue where to past the results or from where to copy them as long as there are results.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
roroman,

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.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
roroman,

More crossing posts - do you now have your own results?

Brian.
0
 

Author Comment

by:reoroman
Comment Utility
God·damn excel. your excel code is good, it works like a hero.. thanks.. but i think after this experiment i will use excel for X O games next time. it took 12 minutes until it finished just column A. so that means it would take 12*41=492 minutes / 60 = 8.2 hours to finish the whole work, which SPSS finish it in 2 seconds.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
hehehe !!! and you were laughing at my solution and treated it slow !!! ???
gowflow
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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?

Edit: Gowflow, care to try it on your's?

Brian.
0
 

Author Closing Comment

by:reoroman
Comment Utility
I can't thank you enough folks... you did great job reflects great personal and sharp minds.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yes it did as you mentioned. But maybe he is not seeing the data flashing coz no screen updating but we see trace in the debug. One issue though h has plenty of zeroes that you onverted to "" and in this case you hv less data and it look akward blank zillilines then some figures then wapo blank .... I think he will not like it much.

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

Author Comment

by:reoroman
Comment Utility
thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Hey reoroman
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
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Gowflow,

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.
0
 

Author Comment

by:reoroman
Comment Utility
gowflow you did also great job, i would give both of you a 500 points but the system do not allow me. thanks again. and best of luck.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reorman,

Any ideas why it's so slow on your PC? Did you try recycling Excel?

Brian.
0
 

Author Comment

by:reoroman
Comment Utility
no i did not as i do not know how to do it,
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Apologies for the jargon. I just meant for you to close and re-open Excel.
0
 

Author Comment

by:reoroman
Comment Utility
i did that now, i will get back with results
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reorman,

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

Brian.
0
 

Author Comment

by:reoroman
Comment Utility
no I am not good with VB. i use primitive way to check the time.. the stop watch .. lol
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
0
 

Author Comment

by:reoroman
Comment Utility
redmondb .. do you have knowledge with SPSS
0
 

Author Comment

by:reoroman
Comment Utility
no, it works with long time... no worries.. thanks anyway.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

but till now 7 minutes has passed and no results.
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.

Edit: BTW, my run was less than 20 minutes.

do you have knowledge with SPSS
I'm sfraid not, but I'm sure you'll find lots of experts who are!

Brian.
0
 

Author Comment

by:reoroman
Comment Utility
thanks ... i will stick with SPSS, really it takes maximum2 seconds... wow
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

Impressive! Why did you bother with Excel?!

Brian.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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.
0
 

Author Comment

by:reoroman
Comment Utility
no thanks, i will go with SPSS and will use excel for trivial things. :-)
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
reoroman,

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

Brian.

Edit: FWIW, the output.Chunk-V.zip
0
 

Author Comment

by:reoroman
Comment Utility
I did not know that would be the outcome, excel is really user friendly, but when comes to that huge time of calculation i would go for SPSS.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Pls let me know if you need any more help. Sorry the outcome of this one was not satissfactorly.
gowflow
0
 

Author Comment

by:reoroman
Comment Utility
sure gowflow... regards
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any help needed ?
gowflow
0
 

Author Comment

by:reoroman
Comment Utility
hey, thanks, sure i will get back to you as soon as i finish some paper work. I need about two weeks. hope we keeping touch. thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
no problem. drop a line in here when u need any help.
gowflow
0
 

Author Comment

by:reoroman
Comment Utility
Thanks sure thing. :-)
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Hi there
I still have this question monitored in case you need help let me know.
gowflow
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now