A novice's solutions corner - Statistics, MS Excel, MS Access, Cognos, and others

iphone vs Android: which group do you belong ?

2011-09-11T23:28:00.000+08:00

I am waiting for iphone 5. Is it worth waiting for ? @__@

"Sorry, the import failed due to a server error. The error code is bX-izedxv".

2011-09-11T23:03:00.000+08:00

You received the above error message when trying to export your Wordpress .xml file into Blogger/Blogspot.

Solution:

Go to http://wordpress2blogger.appspot.com/

and follow the instructions.

It worked for me, but do note that the file must NOT exceed 1MB.

Pathetic Page Views

2011-08-30T16:41:00.000+08:00

This week's page view :

Only 24 page views in total this week (as at 30 Aug 2011).

How pathetic is that.

Should I continue this blog ? =(

How to link data in 2 excel files and combine them into 1 using macro?

2011-08-29T16:55:00.001+08:00

FILE 1
data: Column A, B and C

FILE 2
data: Column A, and D

Use FILE 1 as the primary file.

Final Result: should have A , B C and D together in 1 spreadsheet.

Problem : Look up function but seems like it doesn't work well. the data is jumble up. I still to check using True false function. cos what is avai in A may not be found in B.

Pri file has more information ( in terms of number of names ) than file 2.
name data1 data2 data3

and in file 2:
name data1 data2 data3 data4

Solution:

Sub finder()
Dim rng1 As Range
Dim rng2 As Range
Dim celle As Range
Dim llastrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set rng1 = Range(Sheets("Pri file").[A2], Sheets("Pri file").[A65536].End(xlUp))
Set rng2 = Range(Sheets("file2").[A2], Sheets("file2").[A65536].End(xlUp))

For Each celle In rng1
If (IsError(Application.VLookup(celle, rng2, 1, False))) Then
celle.Offset(0, 17) = "Not found in file2"
Else: celle.Offset(0, 17) = "Found in file2"
End If
Next celle

For Each celle In rng2
If (IsError(Application.VLookup(celle, rng1, 1, False))) Then
celle.Offset(0, 4) = "Not found in Pri file"
Else: celle.Offset(0, 4) = "Found in Pri file"
End If
Next celle

llastrow = Sheets("Pri file").[A65536].End(xlUp).Row
Set rng2 = Range(Sheets("file2").[E2], Sheets("file2").[E65536].End(xlUp))
i = 1

For Each celle In rng2
If celle = "Not found in Pri file" Then
Sheets("Pri file").Cells(llastrow + i, 1) = celle.Offset(0, -4)
Sheets("Pri file").Cells(llastrow + i, 2) = celle.Offset(0, -3)
Sheets("Pri file").Cells(llastrow + i, 3) = celle.Offset(0, -1)
Sheets("Pri file").Cells(llastrow + i, 4) = celle.Offset(0, -2)
i = i + 1
End If
Next celle

Application.ScreenUpdating = True

End Sub

Macro - ADD 3 rows & UNDO Macro to undo the addition of 3 rows

2011-08-28T18:17:00.000+08:00

To do the following :

a) ADD 3 Rows
(or even more rows) by prompting user to enter the number of rows they wish to insert.

b) UNDO MACRO:

1. Undo the addition/insertion of rows

2. when click, it will also prompt: "Do you want to undo adding 3 rows ?"
> Yes | Cancel
if Yes, macro proceed and if Cancel , macro will not run and remain.

Solution:

Option Explicit
Dim StartRow As Long
Dim RowCount

Sub InsertThreeRows()
Dim r As Long, n As Long
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
RowCount = InputBox("Enter number of row insertions", "Row Insertions", "10")
If RowCount = "" Then Exit Sub
If Selection.Rows.Count > 1 Then
MsgBox "Select only one Row!", vbExclamation
Exit Sub
End If
r = Selection.Row
StartRow = r
For n = 1 To RowCount
Range(Cells(r + 1, 1), Cells(r + 3, 1)).EntireRow.Insert
For Each c In Intersect(ActiveSheet.UsedRange, Rows(r)).Cells
If c.HasFormula Then c.AutoFill Range(c, c.Offset(3, 0))
Next
r = r + 4
Next
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Sub UndoThreeRows()
If RowCount = 0 Then Exit Sub
Dim r As Long, rus As String
rus = MsgBox("Do you want to undo adding 3 rows ?", vbYesNo)
If rus = vbNo Then Exit Sub
For r = StartRow To (StartRow + RowCount)
Range(Rows(r).Offset(1, 0), Rows(r).Offset(3, 0)).Delete
Next
End Sub

Mathematical Expectation

2011-08-27T16:58:00.000+08:00

Question 4.53

Suppose that a grocery store purchases 5 cartons of skim milk at the wholesale price of $1.20 per carton and retails the milk at $1.65 per carton After the expiration date, the unsold milk is removed from the shelf and the grocer receives a credit from the distributor equal to 3/4 of the wholesale price. If the probability distribution of the random variable X, the number of cartons that are sold from this lot is:

x	1	2	3	4	5
f(x)	1/15	2/15	3/15	4/15	5/15

Find the Expected Profit.

Solution:

X= No. of cartoons sold from this lot
Y= Expected Profit

Y= Selling Price - Cost Price + Credit from distributor
= 1.65x-5(1.20) +(3/4)[(5-x)(1.20)]
=1.65x -6+3/4[6-1.20x]
=1.65x -6 + 4.5- 0.90x
=0.75x-1.5

E(X) = 0(1/15) + 1(2/15) + 2(2/15) + 3(3/15) + 4(4/15) + 5(5/15)
= 46/15

E(Y) = 0.75 (46/15) - 1.5 = 0.80
The expected profit is $0.80.

Enriching Vocabulary 1

2011-08-27T16:26:00.003+08:00

Words beginning with au~:

1. audacious - daring; often to a degree that is considered foolish.
It was audacious of the student to talk back to his principal, especially when he was clearly in the wrong.

2. augment - to cause to become bigger, better, more valuable
To augment the income of the family, he took up several part-time jobs.

3. augur - to be a sign of something in the future
The lack of lustre in the stars does not augur well for his fortunes.

4. aura - an effect/feeling that seems to surround / come from a person/place
There was a strangely mysterious aura exuding from the ancient museum.

5. auspices - help, support and favour
This concert was held successfully only because of the auspices of the various organisations.

6. austere - plain, without comfort/enjoyment.

The monks are used to an austere life in the mountains.

7. authentic - genuine
The expert judged this to be an authentic piece of work.

8. auxiliary - providing additonal help/support, especially with lower rank or of less importance.
Auxiliary nurses were called in to cope with the sudden emergencies in the hospital.

9. auburn - reddish-brown
My sister has a nice auburn hair.

10. auspicious - giving, promising or showing signs of future success
The Chinese believe red to be an auspicious colour.

Create a macro to convert multiple .txt file and place all data from text to .xls file

2011-08-26T08:24:00.001+08:00

Convert multiple .txt files in a folder
Delimit the .txt files so that there are headers in each column
Format for date (1st column) should be in DD-MM-YY format
Place all data from .txt files into 1 .xls file
Scan through the data in .xls file
Ensure all data are unique (ie. no repeated data in each row)

Solution:

Option Explicit

Public Sub fstest()
    Dim strData As String   'whole file
    Dim varLine As Variant  'single line
    Dim varLines() As Variant  'line data array
    Dim strLine As String    'single line
    Dim strLines() As String  'parsed lines
    Dim strUniqueLineData As String
    Dim dicUniqueData As Object       'with reference you can use New Scripting.Dictionary
    Dim oFSO As Object                    'with reference you can use New Scripting.FileSystemObject
    Dim lngRC As Long
    Dim tsThing As Object                 'with reference you can use TextStream
    Dim strSelectedFolder As String
    Dim fileThing As Object               'with reference you can use File
    Dim strHeaders() As Variant
    Dim lngRow As Long
    Dim lngCol As Long
    Dim strParsed() As String
    
    Const ForReading As Long = 1        'not required if reference used
    Const TristateFalse As Long = 0     'not required if reference used
    
    Set dicUniqueData = CreateObject("Scripting.Dictionary")
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    strHeaders = Array("DATE", "KG-GROSS", "KG-Tot", "BUY", "Tot BUY", "ORDERS", "Tot.Ord", "SELL", "Tot SELL")
    
    'User picks a folder for the text files
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        lngRC = .Show
        If lngRC = 0 Then
            MsgBox "User cancelled folder selection.  Process stopped.", vbExclamation, "No Folder Selected"
            Exit Sub
        End If
        strSelectedFolder = .SelectedItems(1)
    End With
    
    'Iterate through the files in the selected folder, looking for *.txt files
    For Each fileThing In oFSO.GetFolder(strSelectedFolder).Files
        If LCase(fileThing.Name) Like "*.txt" Then
            Application.StatusBar = "Processing " & fileThing.Name
            Set tsThing = fileThing.OpenAsTextStream(ForReading, TristateFalse)
            strData = tsThing.ReadAll
            tsThing.Close
            strLines = Split(strData, vbCrLf)
            strData = vbNullString
            For Each varLine In strLines
                strLine = varLine
                Do
                    strLine = Replace(strLine, "  ", " ")
                Loop Until InStr(1, strLine, "  ", vbBinaryCompare) = 0
                If strLine Like "##-##-## *" Then
                    strLine = "20" & strLine
                    strUniqueLineData = Split(strLine, " ", 2)(1)
                    If dicUniqueData.Exists(strUniqueLineData) Then
                    Else
                        dicUniqueData.Add strUniqueLineData, strLine
                    End If
                End If
            Next
            
        End If
    Next
    
    'Put unique data in array
    ReDim varLines(0 To dicUniqueData.Count, 0 To UBound(strHeaders))
    For lngCol = LBound(strHeaders) To UBound(strHeaders)
        varLines(0, lngCol) = strHeaders(lngCol)
    Next
    lngRow = 1
    For Each varLine In dicUniqueData
        strParsed = Split(dicUniqueData(varLine), " ")
        For lngCol = LBound(strHeaders) To UBound(strHeaders)
            If lngCol = 0 Then
                varLines(lngRow, lngCol) = CDate(strParsed(lngCol))     'Col A is date
            Else
                varLines(lngRow, lngCol) = Val(strParsed(lngCol))       'rest are numeric
            End If
        Next
        lngRow = lngRow + 1
    Next
    
    'Push array data to worksheet
    Application.ScreenUpdating = False
    ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(UBound(varLines, 1) + 1, UBound(varLines, 2) + 1)).Value = varLines
    Application.ScreenUpdating = True
    Application.StatusBar = vbNullString
End Sub

Error in Renaming Folder in Windows Explorer

2011-08-25T18:17:00.000+08:00

Rename a folder but the error states :

Cannot rename ____ : Access is denied.
Make sure the disk is not full or write-protected and that the file is not currently in use.

However, owner is under my name and I have full rights to the folder.
The files in the folder are not even opened.

How ?

Solution:

1.

Download Process Explorer from http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
When Process Explorer is running Press Ctrl + F. This will open the find Dialog Box. Enter the File Name in the Box & Process Explorer will find the Process using that file. Kill that Process in Process Explorer which is highlighted when you select that process in View window which opens & you will be able to rename the file then.

Or download Unlocker from http://www.filehippo.com/download_unlocker/ & select Unlock File from the Right click menu you get. This will close all open File handles & you will be able to rename the File then.

Create a new folder and move the files to it and see if any violations. In case it doesn't work, go to task manager and check if any files is still opened in the 'background' and then end proces. Try again.

Comparing sets of data (lower and upper limit)

2011-08-24T17:51:00.003+08:00

Data set:
0.5, 1.5, 2.1, 0.43, 0.35

condition/base number : 1

Result needed:
To select number nearest to 1 ie.= 0.5 by using macro.

Note: Although 1.5 is also near to 1 (also a 0.5 difference), it must choose the smaller number.
(0.5 is smaller as compared to 1.5)

Solution:

Sub FindClosest()
    Const CONDITION_NUMBER = 1
    Const COLUMNS_OF_DATA = 7
    
    Dim intCount As Integer
    Dim sngNums() As Single
    ReDim sngNums(COLUMNS_OF_DATA)
    Dim sngMinDiff As Single
    Dim sngTemp As Single
    Dim intResult As Integer
    Dim strTemp As String
    Dim lngRow As Long
    Dim strRow As String
    Dim lngBottom As Long
    Dim intTemp As Integer
    
    ActiveCell.SpecialCells(xlLastCell).Select
    lngBottom = ActiveCell.Row
        
    For lngRow = 2 To lngBottom
        strRow = Trim(Str(lngRow))
       ' Populate an array with the differences. PURPOSE: TO REDUCE NUMBER OF LOCATIONS IN WAREHOUSE
       For intCount = 1 To COLUMNS_OF_DATA
           If intCount < 3 Then
               strTemp = Chr(88 + intCount)
           Else
               intTemp = (intCount - 3) / 26
               strTemp = Chr(65 + intTemp) & Chr(62 + intCount - (26 * intTemp))
           End If
           sngNums(intCount) = CONDITION_NUMBER - Range(strTemp & strRow).Value
       Next
    
       ' Start by finding a value that we know is not right that we can use to work our way in.
       ' Get the larger of the differences from the smallest and largest numbers in the source range
       If COLUMNS_OF_DATA < 3 Then
           strTemp = Chr(88 + COLUMNS_OF_DATA)
       Else
           intTemp = (COLUMNS_OF_DATA - 3) / 26
           strTemp = Chr(65 + intTemp) & Chr(62 + COLUMNS_OF_DATA - (26 * intTemp))
       End If
       sngTemp = Abs(CONDITION_NUMBER - (Application.WorksheetFunction.Max(Range("Y" & strRow & ":" & strTemp & strRow))))
       sngMinDiff = Abs(CONDITION_NUMBER - (Application.WorksheetFunction.Min(Range("Y" & strRow & ":" & strTemp & strRow))))
       
       If sngTemp > sngMinDiff Then sngMinDiff = sngTemp
       
       ' Now as we go through the numbers, we know we'll get something that's BIGGER.
       ' Or, if they're all the same, it won't matter which we select.
       intResult = 1
       For intCount = 1 To COLUMNS_OF_DATA
           ' If the abs of the difference is smaller, collect the BIGGER number.
           If Abs(sngNums(intCount)) < sngMinDiff Then
               sngMinDiff = Abs(sngNums(intCount))
               intResult = intCount
           End If
           ' If the abs of the diff is the same, but the original # is lower than the
           ' condition, point to that one instead. RULE OF THUMB: choose nearer to 1 but if btw 0.5 and 1.5, choose 1.5.
           If Abs(sngNums(intCount)) = sngMinDiff And sngNums(intCount) < 0 Then
               intResult = intCount
           End If
       Next
       If intResult < 3 Then
           strTemp = Chr(88 + intResult)
       Else
           intTemp = (intResult - 3) / 26
           strTemp = Chr(65 + intTemp) & Chr(62 + intResult - (26 * intTemp))
       End If
       sngTemp = range(strTemp & strRow).Value
       ' Now intResult holds the index of the closest number that is lower than the condition.
       If COLUMNS_OF_DATA = 1 Then
           strTemp = "Z"
       Else
           intTemp = (COLUMNS_OF_DATA - 2) / 26
           strTemp = Chr(65 + intTemp) & Chr(63 + COLUMNS_OF_DATA - (26 * intTemp))
       End If
       Range(strTemp & strRow).Formula = sngTemp
    Next
End Sub

Random Variables and Probability Distribution

2011-08-20T18:58:00.000+08:00

Question 3.32

The proportion of the budgets for a certain type of industrial company that is allocated to environmental and pollution control is coming under scrutiny. A data collection project determines that the distribution of these proportions is given by:

f (y) = 5 (1-y)^4, 0<=y<=1
0, elsewhere

a) Verify that the above is a valid density
b) What is the probability that a company chosen at random expends less than 10% of its budget on environmental and pollution controls ?
c) What is the probability that a company selected at random spends more than 50% on environmental and pollution control ?

Solution:
a)

Let u = 1-y

Thus, this is a valid density function.

How do I extract selected text from a string of text?

2011-08-18T14:11:00.000+08:00

Data in a cell and need to extract part of it :

Examples:
1. AERO 030405A AUTO 24567AZ 99AM > Extract 67
2. AERO 030405Z AUTO 25611C22AZ 99BM > Extract 11

Solution:
1.
=IF(ISERROR(FIND("C",Sheet1!A1)),MID(Sheet1!A1, FIND("AZ",Sheet1!A1)-2, 2),MID(Sheet1!A1, FIND("G",Sheet1!A1)-2, 2))

2.
=IF(ISERROR(FIND("AZ",Sheet1!A2)),MID(Sheet1!A2, FIND("G",Sheet1!A2)-2, 2),MID(Sheet1!A2, FIND("C",Sheet1!A2)-2, 2))

AS400 / DB2 Programming Tutorial Guide

2011-08-17T22:52:00.000+08:00

My favourite guides are :

Tutorial for AS400
http://www.texas400.com/tutas400toc.html

Tutorial/book on DB2
http://publib.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/CCONTENTS

Let me know if you have more to share.
I am still learning -__-

Probability

2011-08-16T23:34:00.000+08:00

Question 2.26

A California study concluded that by following 7 simple health rules a man's life can be extended by 11 years n the average and a woman's life by 7 years. These 7 rules are as follows: no smoking, regular exercise, use alcohol moderately, get 7-8 hours of sleep, maintain proper weight, eat breakfast, and do not eat between meals. In how many ways can a person adopt five of these rules to follow
a) If the person presently violates all 7 rules?
b) If the person never drinks and always eats breakfast?

Solutions:

a) 7C5 = 7! / (5! 2!) = 7x6x5x4x3x2x1/{(5x4x3x2x1)(2x1)} = 42/2 = 21 ways

b) Never drinks and always eats breakfast >> 2 rules are used.
7-2=5 rules left to choose in total

Only violate 3 out of the 5 rules
5C3 = 5!/(3!2!) = 10 ways

Why are my database files (.dbf) updated but not the cubes (.mdc) ?

2010-09-15T23:25:00.000+08:00

Answer: The cubes are not run/downloaded properly.

(assuming you have scripts to run the cubes)
Double check if the date of the cubes is as at current.
Else do the following :
To click and run the script again - manually
How to run manual:
1. Cognos Powerplay Transformer
2. Open "...... .pyi" (Usually the name is the same as the one in .mdc, just that you have to open the corresponding .pyi file)
3. Click on the cube icon to run the cube.
OR
If you have scheduled a task to run the script automatically (which will run the cubes), then you might also wish to wait for the next update (depending on your scheduler: Control Panel > Scheduled Tasks) and then check if the cubes are now updated.

Problem logging into windows XP (no password-safemode, blue screen persist)

2010-09-10T17:56:00.030+08:00

1)
Try to log into the safe mode and do what was explained/advised in :
http://answers.yahoo.com/question/index?qid=20070409184255AAuAqLp

However, there is no open box for me to type in the passwords in the windows xp blue welcome screen in safe mode.

So i tried ctrl-alt-del and then a dialog box appears with username and password.
Username : administrator
password : control userpasswords2

But the above password doe snot work.

2)
Tried administrator / (password leave blank) , administrator/administrator, etc all kinds of generic combination, but still doesn't log on.

It still goes back to safe mode with 2 avastar showing - 1 for administrator and 1 for user, without any open white box to type in your password. and when clicked on the administrator avastar, it shows "loading personal settings" ...."saving your settings" and then back to original position. --- (a)

And then, if clicked on the user avastar, it will show "logging off....loading personal settings...saving your settings.." and then back to the original position. ---(b)

Previously, prior to logging into safe mode, scenario (b) happens.
so I tried to copy userinit.exe file from another PC, rename the file to wsaudaptor.exe and then copy this file into the systems32 folder. however, it doesn't solve the problem either.

Solution:
1.
Access to another machine.
Put the hard drive (as a slave drive) from the problem computer in another machine and run antivirus and antispyware software on the problem drive. After confirming that the drive is clean, put the drive back into the original machine and run an XP repair from the installation disk.

Free antivirus and antispyware: http://free.grisoft.com

2.
Recover you windows xp by booting with your xp cd as if you were installing windows and choosing the option to recover your system.

3.
Boot to repair console from the xp disk or w2k disk, the commands are:

ren \windows\system32\userinit.exe \windows\system32\userinit.exe.old
copy \windows\explorer.exe \windows\system32\userinit.exe

(or instead of "ren", use "rename" )

4.
if the rename command does not work either, then you may need to download and burn to cd a copy of knoppix. you can do all your file managing using it.

5.
Make sure auto restart is turned off in advanced system properties - startup and recovery.

6.
Then run spybot S&D scans (adware) and virus scans.
Once all is fixed you can re-establish userinit.exe back to its rightful place.
You may think that everything is normal but you should only use the userinit->explorer trick as a tool to fix the underlying problem.

How to delete unwanted columns via macro for data analysis

2010-08-15T23:15:00.000+08:00

1. Place 'X' in Row 2 in whichever columns you would like to delete.

2. Paste the following macro in Module.
(Insert > Module)

Sub del_columns()
Dim ws As Worksheet
Dim lastcolm As Long
Dim i As Long
For Each ws In Worksheets
With ws
lastcolm = .Cells(2, .Cells.Columns.Count).End(xlToLeft).Column
For i = lastcolm To 1 Step -1
If UCase(.Cells(2, i)) = "X" Then
.Columns(i).Delete
End If
Next i
End With
Next ws
End Sub

3. Press Alt+F8.

4. Click Run .

Error in removing adobe illustrator (trial version)

2009-11-20T19:21:00.002+08:00

How to remove adobe illustrator CS3 components after installing the downloaded trial version ?
It states "User cancelled payload installation"

Solution:
The following should be able to clean up :
http://www.adobe.com/support/contact/cs3clean.html

Clearing information in PC

2009-11-08T00:55:00.003+08:00

How do I delete the historical information (besides deleting cookies, IE history , empty recycle bin) to ensure that my personal information / privacy is protected ?

Besides re-formatting the hard disk, are there any other ways ?

Solution:

CCleaner will delete the files.
Download : http://www.filehippo.com/download_ccleaner/

Why can't I open my Cognos impromptu report ?

2009-11-08T00:45:00.000+08:00

You have not opened the right catalog. > Close the report and the catalog. Open the catalog and make sure the right catalog is selected. Then open the report again.
The catalog is corrupted. > Re-do the catalog; check if there is any other catalog that you can use; check if you have a backup.
The catalog is at a different location. > See 1.

Probability

2009-11-07T23:54:00.000+08:00

1. In how many different ways can a true-false test consisting of 9 questions be answered ?

2. A pair of dice is tossed. Find the probability of getting
a) a total of 8.
b) at most a total of 5.

Solutions:

1.
True-False => 2 Options
9C2 = 9! / 2!7! = 36 ways

2.
Sample Space = 6 x 6 =36

a) total of 8 {4+4 , 5+3, 3+5, 6+2, 2+6}
P {a total of 8}
= 5/36

b) at most 5 { 1+1, 1+2, 1+3, 1+4, 2+1, 2+2, 2+3, 3+1, 3+2, 4+1}
P (at most 5)
= 10/36
= 5/18

How to create a "pick from list" in MS excel?

2009-07-27T18:15:00.000+08:00

How to create a "pick from list" in MS excel?

(something similar to combo box)

Solution:
Check this link which gives detailed illustrations:
http://www.contextures.com/xlDataVal02.html