Excel Statistics 52: Probability Laws (by hand notes)

Say you have a table (5x5, for argument's sake)-- along the top is
demand from customer A, on the bottom is demand for customer B. Say the
legend for both the rows and the columns is (0,1,2,3,4), and in the
table itself is the corresponding probability for each pair.

In other words, if you want to see the probability that demand from
both customers was zero, you'd look in the upper left corner of the
array. If you wanted to see the probability that customer A demanded 2,
and customer B demanded 3, you'd go down 3, and over 4 (remember that
0,0 is upper left, not 1,1).

Without using a macro, or manually writing out sum commands, is there a
way to create a total probability distribution table from the table I
have given you? In other words, we know that minimum demand is 0, and
that sum is given by the upper left cell, but the probability that the
total demand is 3 is the sum of cells (0,3), (1,2), (2,1), and (3,0).
The table in this example would be 9 wide, with the probability of 0 on
the left, and 8 on the right.

Perhaps there's a way to match a VLOOKUP, and HLOOKUP, and a few IFs,
maybe, but if anyone can suggest a more automated way or a clean
syntax, I'd be grateful.

Thanks!

Hello everyone,
I've been increasingly working with Excel for my job. With some tasks becoming repetitive, I thought that I should start learning Macros to make what I have to do as quick and efficient as possible.

I don't have experience working with Macros, although I've been studying VB.Net for several months now, so that should speed along my learning process at least a little bit.

One of the first Macros I hope to write is something that I believe would help big time with some of the sales report that I have to do.

One simple report for example is I take the weekly sales figures for each of our sales people and put them into a spreadsheet every month, while comparing it to the previous year... It's not a difficult report to put together, but there's one issue that makes it a pain in the rear end. In the software that we use to store our sales reports, if a sales person has $0 in sales for the date range that I've specified, they will not show up at all. This makes me unable to just copy and paste columns because a sales person may be in the data one week and not in the data the next. I want to make a Macro that will compare a column listing sales people to a list of all of our sales people and create rows for sales person that is not already included in the column.

Since I'm new to this, I was hoping somebody could give me either some sample code or a suggestion on how to best go about accomplishing this. Thank you in advance for any helpful feedback.

I have tinkered with this for a few days. I am getting the header row to copy and the first sales person (SP) as a secondary header. The problem is that the SP has more than one row of data. The number of rows is arbitrary; some have one, some have many, and in a lot of cases the SP is a blank cell. The macro executes and creates a separate workbook for the same sales person over and over until I break execution of the macro. Any thoughts most welcome.

Sub CreateWorkbooks()
Dim WBO As Workbook ' original workbook
Dim WBN As Workbook ' new workbook
Dim WSO As Worksheet ' original worksheet
Dim WSN As Worksheet ' new worksheet

Set WBO = ActiveWorkbook
Set WSO = ActiveSheet

finalrow = WSO.Cells(Rows.Count, 1).End(xlUp).Row + 1

LastSP = Cells(2, 53)

StartRow = 2

For i = 2 To finalrow
ThisSP = WSO.Cells(i, 1)
If ThisSP = LastSP Then
' do nothing
Else
' We have a new salesperson starting
' Copy all of the previous rows to a new workbook
LastRow = i - 1
RowCount = LastRow - StartRow + 1

' Create a new workbook.
Set WBN = Workbooks.Add(Template:=xlWBATWorksheet)
Set WSN = WBN.Worksheets(1)

' Set up the headings for the report
WSN.Cells(1, 1).Value = "Prospect List"
WSN.Cells(1, 1).Font.Size = 14

WSN.Cells(2, 1).Value = WSO.Cells(StartRow, 53)
WSO.Range("A1:BD1").Copy Destination:=WSN.Cells(4, 1)

' copy all of the records for this salesperson
WSO.Range(WSO.Cells(StartRow, 1), WSO.Cells(LastRow, 56)).Copy Destination:=WSN.Cells(5, 1)

FN = LastSP & ".xlsx"
FP = WBO.Path & Application.PathSeparator

WBN.SaveAs Filename:=FP & FN
WBN.Close SaveChanges:=False

LastSP = ThisSP
StartRow = i
End If
Next i
End Sub

Is there a hand tool in excel to click and drag a sheet around the window? It would be similar to the hand tool in Adobe Reader. Linked to a short cut, this would be the fastest way to get around a sheet.

hiya all i was wndering if anyone could help me i need to work the probability of number using a percentage for expample 50 sales has a probability of occuring 40% and 60 sales has probability of occuring 5% and so on, can any explain how i need to do this im new to excel ,
thank u

I have a pack of cards and already drawn some cards.

I have 40 cards left: 12 diamonds, 11 hearts, 10 spades and 7 clubs.

The probability of the next 3 cards being (in order) a diamond followed by a club then another diamond is (12/40)*(7/39) * (11/38).

The answer is 1.5587%. What formula should I or could I use to get this result? I'd imagine it would be a nested formula that's not going to be easy to decipher!!

My problems I'm encountering when trying to get Excel to work out the probability for me without me having to hard-wire the figures in to the formula:

The cards are not being replaced, so when a diamond is drawn out, there is one less diamond in the pack to take into consideration when looking for another diamond. It's easy when, say, there are the same number of each suit left remaining but this is not the case!!

I can see a long-haul solution would be to list every possibility then lookup the desired combination but time/speed/hassle.... there's another easier way, isn't there?

Gurus,

When i ran this lines of codes, my microsoft excel hangs.
What I am trying to do here is.. in sheet1 cell A1 receives a value from a device, either 1 or 0.
When the value is 0, do nothing, when the value is 1. Copy the data range A5:B10 and paste it in Sheet2 cell A1. The program will keep on doing this, copying and pasting when it receives a 1 in sheet1 cell A1.
And also, my paste function is not working, any idea how i can do it?

Do While Sheet1.Cells(1, 1).Value = 1
'Range Selected
Sheet1.Range("A5:B10").Copy
'Paste Range According to Column available
Sheet2.Cells(y, 1).Special (xlPasteValues)
y = y + 9
Loop
End Sub

Thanks..

Hello all. First post here and I'm looking for a little bit of help .

I've started on a project for a Prob & Stat class I have and I'm stuck trying to figure out how to display a lognormal probability distribution on a Histogram.

In the attached .xlsx on sheet "C1 HIST" you can see the random variable seeds, the loginv to get the full numbers (column C), the BIN I used for the histogram, and my attempt at the "lognormaldist" values. On sheet 3 you an see the actual commands I used to generate the numbers. I don't think I'm using the "lognormaldist" properly because the values in Column E of "C1 HIST" keep going up instead of spiking up quickly and then coming back down.

Toward the bottom of this sheet you can see what an add-in called "EasyFitXL" shows the lognormal distribution to be, but I'd like to know how to do it without an add-in.

I also attached the project statement (I'm trying to do the probability distribution part of step 2 of "Progress Report 1". Right now I'm just trying to figure out everything for Random Variable C1, and once that's done, the rest will be easy. You can also see the distribution type, CV, and Mean on the table in the .pdf (seen at the upper right of sheet "C1 HIST").

If you guys have any help/input it's be greatly, greatly appreciated.

Thanks so much!

Hi all, my first post so i hope you can help!

I have some data for quotes a company has been sent, with a number of variables, for example qty of items requested, value of quote, length of time it took to return quote etc... And finally a yes or a no column as to whether the business was won.

What i want to do, but i'm having touble getting head around how, is produce some sort of table of probability for success of a quote.

For example, if a quote comes in for 50 items, and we return the price within 20 days, what is out chance of winning the bid?

Any ideas, do i need multiple tables for each variable with a yes or no against each, then compare these, or is there some fancy statiscal test i can do?

Any ideas?

Thanks,

I have a data set based on an uneven distribution of cards in a 60 card deck, each with a value ranging from 0-5.

I need to build a probability distribution of all the random samples of 10. So, how many random sets of 10. have an average value of 1 (or a total of 10) etc.

Then, I need to build a variable distribution so that it changes as I change the sample size. So, with a sample of 20 cards, what does the distribution look like?

Can this be done in excel? Should I post what I have now to work with?