Free Microsoft Excel 2013 Quick Reference

Probability Question:closely correlated data

I have two closely correlated data sets (correlation coefficient 0.68), and I want to set up a probability calculation: If the value in one set is less than a certain number, what is the probability that the value in the other set is less than a certain number?

How do I do this?

Many thanks -

Post your answer or comment

comments powered by Disqus
I have a spreadsheet which will be access by others and they will enter rows of information. I want to ensure that they do not half complete rows. Therefore I would like to know if I can prevent the workbook from closing unless data is entered into each cell in the specifed columns?


I have a question about accessing data in various worksheets.
I have many worksheets (one for each employee).

Each worksheet contains many formulas and the final result is the total number of hours worked for each employee.

I would like to create a new sheet that has a list of each employees name and the total hrs worked next to the name of the employee.

Is it possible to copy a formula from each worksheet into the new worksheet?



I need some help finding out how to correlate different kinds of data. I've done a survey where I have a lot of results. Now I want to see if there's any connection between answering No/Yes in one question and answering No/Yes in another question. I've tried different moves but without any good results. I have the office 2007 version.
If anybody can help me quickly I would be more than greatfull.
Helge (from Denmark)


In each period (#1 - 10) the probability that an event will happen increases 3% from the period prior. What is the probability that it will happen, or has already happened, in any given period (e.g. in period #3, the probability that the event happens at least once in period 1, 2 or 3)? Is approach 'A' capturing the cumulative nature of it correctly? Is 'A' more correct, or another method? I've attached an example sheet with data for my question. Any help appreciated!


Hello -
I'm new to this group as well. I'm on Excel 2003, Win XP Pro and I'm
fairly familiar with the excel interface, but probably still a newbie.
I'm doing a running balance, like a checkbook, with deposits and
withdrawals etc. I've set that up fine, but I'd like to be able to correlate
and continue data between sheets because I've set the worksheet up to have 8
sheets within it (if I'm using the wrong terminology, this is one (1) excel
worksheet file with 8 sheets within it - that's 8 sheet tabs at the bottom).
In specifics, I'd like to have , say, M2 of Sheet2 pick up the data/value of
the balance at the end of the former sheet, say, from L100 of Sheet1
automatically. Is this possible ?
I've also had similar issues with my tax expense statement where it would
come in handy to continue sum formulas between sheets.
I appreciate any help anyone could offer. I hope my terminology was clear
enough. Thanks,...Frank Bright

I have a spreadsheet which I am pretty much finished with as far as the cell
formulas are concerned, but now there has been a wrench thrown in the works.
I have a pretty simple data set that my calculations are based on. Column A
is date, B is temperature, C is humidity, and D is pressure. The
calculations are all based on these 4 criteria. My problem is that now I am
faced with about 30 data sets to run these calcs for. They are all in CSV
files which I can use as is, or convert to XLS files.

My question is: is there a way to use the main spreadsheet with all the
calculations and "call" one of the data sets in to the main spreadsheet? I
don't know if a pivot table would do this or not, as I haven't done much
with them, but I can learn. I want to be able to basically pick one of the
data sets, run the macro to do the calculations, and then save the output as
a web page. All the data calculations end up on one sheet for each data set.
Having all of the data sets in one spreadsheet probably isn't a good option
either. These data sets are up to 5000 rows, but at the most 4 columns.

Any help is appreciated.


Hello Everyone,

I have a quick question. I am using Excel to do some statistical modelling and I am wondering if anyone knows if the PERCENTILE function in Excel has the ability to determine the most suitable probability distribution model that best describes the data set before giving an answer. No reference is made to the probability distribution model in the Excel help file on PERCENTILE function. Does anyone here definitively know how the PERCENTILE function works? Does it base the answer on the probabilty distribution function or does it simply order the data set and give the answer based on the rank? Any clarification is welcome.

hi there,

I have an array of data (A,B,C,D,...,H) and a correlation/relationship data table (asymmetric data).
Out of the array data, i need to make a combination of 3 data out of 8 AND input the relationship data according to the combination.

I found a problem to take the reference from correlation data.

to help you understand my issue, i attached the file with this posting.

i am looking forward for your advice.

Can anybody help out. I am using the correlation coeffecient function. an example=correl([1,2,3},{4,5,6}). My data is in column A and column C. Is there anyway to input my data in column A into the first set of brackets and input coulmn C into the second set of brackets other than manually typing it in?

Based on what the area code that is in column H, column I should populate what STATE the area code correlates to, which is on sheet labeled AREA Codes, in column A (area code)and B (State).

Workbook contains several sheets - initial sheet contains Column H which is populated with area code information. Column I requires the formula to populate the state which is displayed on a sheet labeled Area Codes. The Area Codes sheet contains column A which is populated with area codes and Column B which is populated with the corresponding State.

Sample of Data:
Column H Column I

Area Codes Sheet

Column A Column B
Area Code Region
201 NJ
202 DC
203 CT
204 MB
205 AL
206 WA
207 ME
208 ID
209 CA

I have two data sets one measured and one modelled and I am testing a conceptual model that proposes that only where the tails of the probability distributions of each data set crosses will you experience an event. I'm enclosing an example file which contains both data sets and some descriptive statistics as well as the associated histograms and a diagram of what I'm ultimately trying to achieve.
My question is how I go from the data sets I have with such a large discrepancy between them to plotting their probability distributions?

Thank you for taking the time to look into this for me,


I am using the list option for Data Validation and have the following questions:

1. What exactly is the purpose of the 'ignore blank' option? I played with it a while but can not observe any difference?

2. Can I control the number of records which my resulting list displays? I think a Data Validation list display 7 or 8 items? Can I increase or decrease this?

3. I generally leave extra room in my underlying data range which I use for lists (using Data Validation). This has it's advantages but the annoying thing is that when I pull down a such a list while data entry, the blank entires are also listed and sometimes the list start from the end (showing the blank lines/entries) and thus making me to scroll all the way up to the top of the list

Can someone kindly assist.


- J

I have data in column A. The data contains a name, an underscore and initial/s. For example: rebecca_rt, john_j, etc... I'm trying to pull only the characters to the right of the underscore. I played around with the below formula that I found under a similar question thread. But, it doesn't quite work. As it is now, it returns ecca_rt when I just want rt. I'm sure this is probably really close, so any help in correcting the formula would be appreciated.




I have code that pulls data in from closed workbook to sheet I have nominate in active workbook. This then is pulled up on a user form where it can be updated... My problem is that I don't know how to update this back to the closed work finding the correct line to ammend.
I have been playing with this code below.....

    Dim Db As DAO.Database 
    Dim rs As DAO.Recordset 
    Dim strSearch As String 
    strSearch = Trim(ComboBox4.Text) 
    Set Db = OpenDatabase("H:cost recovery dataRecovered Costs.xls", False, True, _ 
    "Excel 8.0; HDR=YES;") 
    Set rs = _ 
    Db.OpenRecordset("SELECT * FROM [recdata1$] WHERE [Rdeptref] LIKE '" & strSearch & "*'") 
    Worksheets("recdata1$").Cells(ComboBox4.ListIndex + 2, 7) = Ncode.Text 
    Worksheets("recdata1").Cells(ComboBox4.ListIndex + 2, 8) = ComboBox3.Text 
    Worksheets("recdata1").Cells(ComboBox4.ListIndex + 2, 9) = Nctc.Text 
    Worksheets("recdata1").Cells(ComboBox4.ListIndex + 2, 10) = Raddress.Text 
    Worksheets("recdata1").Cells(ComboBox4.ListIndex + 2, 11) = Rr1.Text 
    Worksheets("recdata1").Cells(ComboBox4.ListIndex + 2, 12) = Rr2.Text 
    Worksheets("recdata1").Cells(ComboBox4.ListIndex + 2, 13) = Rr3.Text 
    MsgBox "One Record updated to Recoverable Database" 
    Unload UserForm4 
    UserForm2.ComboBox1.Text = ComboBox4.Text 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Also could someone tell me how to update this thread to solved I can't find the button.... silly me.

look forward to any help

Hi guys, had a weeks break, back to it now. 2 questions for you.

1) I have two workbooks linked, one of which is purely a data book, which the user is never allowed to access, but which must open when the other workbook is opened. I have written and tested the code for this, and its fine. However I cannot seem to find the right code, for closing this data workbook when the user closes and saves the one they will be using.

The two books are, OTentry.xls and TVille_Data.xls

I need to close TVille_Data.xls from within OTentry.xls, and it has to close without being saved.

2) with a user form, can I get the form to close as soon as the user has completed the required action? specifically a userform with a list box with options., I want it to close as soon as the user has selected one of the available options.

PS is it possible or sensible to remove the 'x' from the top right hand corner of userforms?

Thanks guys,


Hi All,

This is probably really simple for you guys but I'm struggling with it. I need an entry that will calculate the number of entries raised during a given month and the number closed during a given month and display this on a separate sheet.

The result I'm after is a box that says x opened and y closed.

The data table looks like this:

NoDate RaisedStateAgeTarget Date10166518-Aug-08Open201-Sep-0810166015-Aug-08Open529-Aug-0810165915-Aug-08Open529-Aug-0810164614-Aug-08Open628-Aug-0810164514-Aug-08Open628-Aug-0810163813-Aug-08ClosedN/A27-Aug-0810163513-Aug-08ClosedN/A27-Aug-08



Hi. This is, i think, a tough one. I know the solution is kind of a fuzzy match marcro....It's a little bit hard to explain so i will be as clear as i can. Ok so there is the data. Please refer to this image. The question will follow.

I have an excel sheet with information about music.They're is 798 entries.

I have the info about the ARTIST, the TITLE and i have also the IMAGE path information. The thing is, the infos on the ARTIST and the TITLE columns are ok and they are sorted the right way. I can't change that order, absolutly not. But in the @IMAGE column I have a list of image path that correspond to the songs on the 2 first columns. This column is all mixed up and unsorted.

But the filename may contain one word or two in the one of the 2 other columns. Sometimes the filename of the image contains the title, sometimes the artist, sometimes both and sometimes none of them.

For example, in the second row, in the IMAGE column, i have the filename "Nordbanhof.jpeg". I am pretty sure that i will found this info somewhere in the TITLE column.

My question is : is there a way inside or outside of excel to quickly assisgn the most possible entries of the IMAGE column to their correspondant row in the 2 other columns ?

I know it's a little bit hard to explain, but i am sure there is way. Maybe in excel or maybe in an external software. The thing is i don't want to manualy assing the right filename to the right entry..sure you guess what kind of job it involves...


I have a question. I havent actually started working on this project yet because i wanted to see how it would work, I'm sure someone here has done it before.

Its regarding links in an excel file to other excel files. I have an excel file for each customer (about 3,000 files currently). I also have a database with all of the names in it. What i would like to do is make a report of each customer. I would like the report to contain certain information from each customer file (I probably wont be collecting data from all 3,000 files, but maybe files from this month). My idea was to create a link from my main database of names to each customer file. My question is: Is this a good idea? would updating all the links cause instability in excel? Is there a better way to do this? I also thought about possibly making a macro that opens each file and gets the data. Would this be a better solution?

I think access would be a better option for me right now, but its a bit late for that and i have no idea how to use it

Thanks very much.


Me and my team have been given a tricky macro to put together for another dept. I've been playing around with loops but cannot get an effective one - here is the question:

I have data arranged in rows like this

1500 Monthly Salary

What I need to do is copy from cell A1 and B1, paste into cell B2 and C2, preserving the nformation in A2 and D2. This part is straightforward, but then the report has multiple groups of data, each with a blank row between them (eg, where Monthly salary and it's number appear, there are 1100 Overtime further down in A671, and B671 respectively) From what I can tell in the report the number of groups, and their length will vary. I am tempted to tell them to do it manually but thought I would post here and see if anyone has any ideas.

Let me know if you need more info

First off....if this turns into a double post, I apologize....I tried to post
this about an hour ago but something messed up and I really don't think it
went through.

Project: I've got a workbook...7 sheets...only one that is available
(easily) to the operator. This operator sees the opening page and clicks on
one of several macro'd buttons....that takes him a data entry sheet and a
data-form opens....when the data is entered, he/she closes the data-form and
the macro prints the page, erases the entered data and returns to the opening
page. I've used an open workbook module to get rid of the tabs, formula
bars, etc, so, unless he/she knows Excel fairly well, it'd be a little
difficult for them to corrupt the programming.

I've also incorporated a before_closing module, which resets the formula
bars, tabs, etc...then closes the program...

Problem: There is no need ever for the operator to save this workbook...but
I found that when it goes into the close routine, the standard window "do you
want to save changes" pops up.....and if you hit cancel at that point the
book stays open and all the formula bars, tabs, etc are now active.

Question: Is there a way to bypass that "save" window?

Thanks in advance and again, if this is double posted, I do apologize,

Have a great day,

Hi all,
I have seen similar problems in some old threads and other forums, but
no workable solution so far.

The problem I have is this:

I use Excel worksheet as a data source for Word mail merge, and I
wrote a sub in Excel VBA to open the word document and link it to it's
data source, which works fine. But when both Word and Excel are
closed, there is still an Excel process in the memory, it shows up on
Task Manager processes list. This ghost process causes opening failure
for all future instances of Excel, untill it is manually terminated or
PC is restarted.

The code in question is below.

Sub mergeAndViewLetters()

Application.ScreenUpdating = False
setMergeVariables 'sets all the global variables not dimensoned

Dim sDataFileName As String
Dim sSQL As String
Dim oWApp As New Word.Application
'early binding, requires MS Word Library
Dim docLetterFile As Word.Document

sDataFileName = ThisWorkbook.Name
sSQL = "SELECT * FROM `MergeData$`"

oWApp.Visible = True
Set docLetterFile = oWApp.Documents.Open(stProjectPath &

docLetterFile.MailMerge.OpenDataSource _
Name:=stProjectPath & sDataFileName, _
LinkToSource:=True, _
Revert:=False, _
Connection:=stProjectPath & sDataFileName, _


'tidy up - release references
Set docLetterFile = Nothing
Set oWApp = Nothing

End Sub

I made an attempt at releasing all the references. I even tried
closing the data source or pointing it to a non-Excel dummy data
source on closing of the Word document (not in this version of the
code) - none of that worked.

There was a suggestions to use only fully qualified references to
Excel objects, I might try that, but it will not be easy and there is
no guarantee that I won't miss something.

Need urgent guru intervention...
Thank you

This might be a "junior" type of question based on the questions/responses
I've read, but I really need help. I have four workbooks(representing
departments within our company) with multiple worksheets each(4-12 sheets).
Each day I enter sales and payroll data onto each page. What I'm looking to
do I have one (workbook or worksheet) where data is pulled from each workbook
worksheet and constantly updates the data. It would make life much easier
for me if I could enter data as normal each day, and then "magically" have a
completed weekly summary sheet by the end of the week for a Monday

I apologize if this is a simple question, I can tell I'm not as advanced as
most here. I'm more or less self-taught, having only recently learned how to
perform simple calculations. Excel is a fascinating tool, and I can't wait
to learn more.

(a bit similar to the xWizardx post re: calling date data; ultimately I could not find solution from this thread)

Workbook A contains, among a total of 20-30 named ranges, the named range "MyName".

I would like to, upon command button click (which I'm good with), read this file, copy the data ("MyName" and the others), and return the data to a specified worksheet ("DataDump" - Cell A1 filling down) in my Active Workbook.

I am currently working with / attempting to modify the following:

Sub ImportData()
' Import Macro
Dim strWorkbookName As String
Dim strImportFileName As String
pAbort = False
'Get the current workbook name
    strWorkbookName = ActiveWorkbook.Name
' Open file for import - allows user to specify file containing the data needed.
    FileName = Application.GetOpenFileName(FileFilter:="Excel Files (*.xls),*.xls", Title:="Select
    If FileName = False Then
        MsgBox "No file was selected! Exiting..."
        pAbort = True
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Workbooks.Open FileName
    strImportFileName = ActiveWorkbook.Name
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    MsgBox ("Data successfully imported")

End Sub

This code does successfully retrieve/paste the "MyName".

1. Is there a way to collect all the data at once, and then paste all the data at once?

2. Is there a way to do this without actually opening the queried file?

3. If not, is there a way to override the 'must save prompt' of the queried workbook after
Thanks!! ~AS

This might be a "junior" type of question based on the questions/responses
I've read, but I really need help. I have four workbooks(representing
departments within our company) with multiple worksheets each(4-12 sheets).
Each day I enter sales and payroll data onto each page. What I'm looking to
do I have one (workbook or worksheet) where data is pulled from each workbook
worksheet and constantly updates the data. It would make life much easier
for me if I could enter data as normal each day, and then "magically" have a
completed weekly summary sheet by the end of the week for a Monday

I apologize if this is a simple question, I can tell I'm not as advanced as
most here. I'm more or less self-taught, having only recently learned how to
perform simple calculations. Excel is a fascinating tool, and I can't wait
to learn more.

No luck finding an answer? You could always try Google.