Solved

# excel macro to do math formula

Posted on 2013-02-06
627 Views
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
Question by:reoroman
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 34
• 27
• 15

LVL 26

Expert Comment

ID: 38862190
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

gowflow earned 250 total points
ID: 38862196
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

ID: 38862468
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

ID: 38862508
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

ID: 38862560
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

ID: 38862620
Thanks, reoroman.

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

Thanks,
Brian.
0

Author Comment

ID: 38862671
sorry i forgot about that.. they are 41 columns..
0

Author Comment

ID: 38862674
0

LVL 26

Expert Comment

ID: 38862682
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

ID: 38862705
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

ID: 38862759
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

ID: 38862843
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

ID: 38862865
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

ID: 38863312
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

ID: 38863387
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

ID: 38863600
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

ID: 38863698
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

ID: 38864087
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

ID: 38864184
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.
- 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

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)),"""")"

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

xSource.Delete

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

Author Comment

ID: 38865390
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

ID: 38865392
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

ID: 38865412
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

ID: 38865457
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

ID: 38865471
reoroman,

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

ID: 38865520
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

ID: 38865527
Thanks, reoroman, but I still need answers to my questions. Are they unclear?

0

Author Comment

ID: 38865549
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

ID: 38865558
please upload the new formula with your code here as gowflow did, and i will attach the data to it
0

Author Comment

ID: 38865564
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

ID: 38865566
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

ID: 38865766
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

ID: 38865772
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

ID: 38865850
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

ID: 38865902
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
``````

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

ID: 38865951
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

ID: 38865960
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

ID: 38865963
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

redmondb earned 250 total points
ID: 38865971
reoroman,

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

Brian.Chunk-IV.xlsb
0

Author Comment

ID: 38865975
so if we have column from A to AO the answer should be placed on AR to CF
0

LVL 26

Expert Comment

ID: 38865981
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

ID: 38866017
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

ID: 38866024
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

ID: 38866030
roroman,

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

Brian.
0

Author Comment

ID: 38866039
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

ID: 38866052
hehehe !!! and you were laughing at my solution and treated it slow !!! ???
gowflow
0

LVL 26

Expert Comment

ID: 38866069
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

ID: 38866085
I can't thank you enough folks... you did great job reflects great personal and sharp minds.
0

LVL 29

Expert Comment

ID: 38866086
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

ID: 38866092
thanks
0

LVL 29

Expert Comment

ID: 38866098
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

ID: 38866104
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

ID: 38866120
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

ID: 38866129
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

ID: 38866138
reorman,

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

Brian.
0

Author Comment

ID: 38866142
no i did not as i do not know how to do it,
0

LVL 26

Expert Comment

ID: 38866146
Apologies for the jargon. I just meant for you to close and re-open Excel.
0

Author Comment

ID: 38866154
i did that now, i will get back with results
0

LVL 26

Expert Comment

ID: 38866167
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

ID: 38866193
no I am not good with VB. i use primitive way to check the time.. the stop watch .. lol
maybe i have something wrong with my desktop. I will try it later on my laptop
0

Author Comment

ID: 38866203
redmondb .. do you have knowledge with SPSS
0

Author Comment

ID: 38866253
no, it works with long time... no worries.. thanks anyway.
0

LVL 26

Expert Comment

ID: 38866256
reoroman,

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

ID: 38866274
thanks ... i will stick with SPSS, really it takes maximum2 seconds... wow
0

LVL 26

Expert Comment

ID: 38866282
reoroman,

Impressive! Why did you bother with Excel?!

Brian.
0

LVL 26

Expert Comment

ID: 38866310
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

ID: 38866317
no thanks, i will go with SPSS and will use excel for trivial things. :-)
0

LVL 26

Expert Comment

ID: 38866331
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

ID: 38866454
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

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

Author Comment

ID: 38867099
sure gowflow... regards
0

LVL 29

Expert Comment

ID: 38901913
Any help needed ?
gowflow
0

Author Comment

ID: 38903157
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

ID: 38904428
no problem. drop a line in here when u need any help.
gowflow
0

Author Comment

ID: 38904931
Thanks sure thing. :-)
0

LVL 29

Expert Comment

ID: 38908847
ok
0

LVL 29

Expert Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.