Excel Statistics 64: Cumulative BINOMDIST with IF Function for Cumulative Distribution Charts

My job requires me to establish the appropriate sample size during very expensive flight testing (i.e. can't afford to do more testing than the minimum required to establish the appropriate confidence). I have a nomograph that was given to me that we use to calculate the right value given confidence and demonstrated reliability (i.e. results). The nomograph can be found at: https://acc.dau.mil/docs/dtepi/pns/doc/nomogr/bdi.pdf.

Assuming we want to show that we have 80% confidence (right axis) that we'll succeed 80% of the time (left axis, also called reliability), I connect those values on each side with a straight edge and the chart shows that with zero failures (upper right boundary) we need 7 trials. If we have one failure (upper left boundary) the chart shows that we need 12 trials (11 successes). I was provided a formula that defines the zero failure curve [n=Log (1-confidence)/Log (reliability)]...What I need is a more generic equation or excel function that will generate these curves with failures not equal to zero.

Any statistics experts out there that can help me?

Thanks

I am trying to use the excel function BINOMDIST to calculate what the probabilities would be for each correct score in a best of 14 darts match where the match stops if a player reaches the required 8 legs.

To work out these probabilities I am using the probabilities for each player to win a single leg when they are throwing first and then multiplying this by the number of possible combinations of this particular score occuring. This part I believe I have worked out correctly. Bear in mind, for Player A to win 8-1 then player B can only win one of the first 8 legs or else Player A would have already won the match 8-0. For any novices to the game of darts each player throws first in alternate legs so we also need to take in to account the player who is throwing first, I intend to identify this by marking either cell A2 or A3 with a 1.

On the attached example I believe I have correctly formulated for Player A to win 8-0 but am struggling with the remaining scores.

Could someone have a look at the example and see if they can point me in the right direction on how to formulate these remaining probabilities.

Thanks,
Adam.

Hi all,
How do I create a normal curve graph and cumulative distribution graph using excel?Thanks

Hi all.

I'm not the most advanced with using the chart function within Excel and want to know if there's any way to highlight a load of data and create a chart which adds up the data to produce a cumulative graph without creating a new line and adding them up myself???

Ta

Greetings Power Users!

XP, Excel 2003 with stats ad in

Yes, this is an assignment AND I have the answers BUT I want to know how to get there (not wating your time). Taking online statistics and everyone in class using calculators, I am the only Excel fan.

I approach problem as BINOMDIST and cannot get answers to jive.

80% of new products fail within two years. 66 new products introduced, what is the probability that within 2 years

(a) 47 or more will fail? FALSE = PMF
(b) 58 or fewer will fail? TRUE = CUM
(c) 15 or more will succeed?
(d) fewer than 10 succeed?

Probability = .8, number of trials = 66, 2 years is a distractor, x = period:

(a) does not match key: 1-BINOMDIST(47,66,.8,FALSE)? When equal to or greater than use 1-? Should I use ((47+1),66,etc.?

(b) For fewer, should I use TRUE? Doesn't TRUE indicate value up to x?

(c) duplicate formula and conventions for (a)

(9) should be straight forward but is it TRUE?

As stated, I have the answers from the key, I just want the confirmation of the methodology OR is the answer key wrong (yes, I've seen it before)

Answer Key: (a) .9738 (b) .9599 (c) .3446 (d) .1271

I have been through the fx options for three days and I tried POISSON, and every other tab I could until I just could not syand anymore!

Thanks for your thoughts

Hi guys. I am a new member-i had a friend recommend excel forum and he said you guys were very knowledgeable about helping with excel.

I have a (hopefully) quick question
I have a column "M" with 2500 cells of either 0,-1,1. I want to create a column in N, that keeps a cumulative count of the number of times -1 or 1 is reached and resets every time a new value is reached (and disregards the value of 0)
My data starts like this: I want it to say this:
0 0
1 1
1 2
0 0
-1 -1
-1 -2
-1 -3
1 1
1 2
1 3
1 4
1 5
0 0
etc...

Thanks guys I really appreciate all of your help!

The function below takes data from a query and dumps the data into a new table and provides monthly (from the query) and cumulative values (which are calculated here). There seems to be something wrong with the order in which the function is adding the monthly values to get the cumulative monthly values as the last record that has cumlative data is always correct, but if you add up the monthly values in the Oct, Nov, Dec, etc. order, and compare them to the monthly cumulative values, the monthly cumulative values do not jive. I know this is clear as mud, but if any could help me sort through this, I'd be so appreciative!!!

Thanks!

Code:

Option Compare Database

Public Function TableExists(ByVal name As String) As Boolean
    On Error Resume Next
    TableExists = LenB(CurrentDb.TableDefs(name).name)
    End Function
Public Function OnePager()
    Dim curCom, curObs, curCost, curPlanned, curBase, runningCom, runningObs, runningCost, runningPlanned, runningBase As Long
    Dim projectcode, benefitor, costelement, sql As String
    Dim period, x, full(13) As Integer

    Dim db As Database
    Dim rs As Recordset
    
    Set db = CurrentDb
 
   
    If Not TableExists("tblOnePager") Then
        db.Execute ("CREATE TABLE tblOnePager ([Project Code] TEXT (40), Benefitor TEXT(10), [Op Plan Description] TEXT(20), [Period Number] TEXT(5), [FY Commitment] FLOAT, [FY Obligation] FLOAT, Cost FLOAT, Planned FLOAT, Base FLOAT, RunningCom FLOAT, RunningObl FLOAT, RunningCost FLOAT, RunningPlanned FLOAT, RunningBase FLOAT )")

    Else
        db.Execute ("DELETE FROM tblOnePager")
    End If
    
    Set rs = db.OpenRecordset("SELECT * FROM [qryOnePager] ORDER BY [Project Code], [Benefitor], [Op Plan Description], [Period Number]")
        runningCom = 0
        runningObl = 0
        runningCost = 0
        runningPlanned = 0
        runningBase = 0
        
    
    For x = 1 To 12
        full(x) = 0
    Next x
     
    Do While Not rs.EOF
        curCom = 0
        curObl = 0
        curCost = 0
        curPlanned = 0
        curBase = 0
        
        
        If Not IsNull(rs![FY Commitment]) Then curCom = rs![FY Commitment]
        If Not IsNull(rs![FY Obligation]) Then curObl = rs![FY Obligation]
        If Not IsNull(rs![Cost]) Then curCost = rs![Cost]
        If Not IsNull(rs![Current Plan]) Then curPlanned = rs![Current Plan]
        If Not IsNull(rs![Baseline Plan]) Then curBase = rs![Baseline Plan]
        

        
        If projectcode Like rs![Project Code] And benefitor Like rs!benefitor And opplan Like rs.Fields("Op Plan Description") Then
            runningCom = runningCom + curCom
            runningObl = runningObl + curObl
            runningCost = runningCost + curCost
            runningPlanned = runningPlanned + curPlanned
            runningBase = runningBase + curBase
            
         
            full(rs![Period Number]) = 1
        Else
            If projectcode  "" Then
                For x = 1 To 12
                   If full(x) = 0 Then
                    db.Execute ("INSERT INTO tblOnePager ([Project Code], Benefitor, [Op Plan Description], [Period Number], [FY Commitment], [FY Obligation], Cost, Planned, Base, runningCom, runningObl, RunningCost, RunningPlanned, RunningBase) VALUES ( '" & projectcode & "', '" & benefitor & "', '" & opplan & "', '" & x & "', '" & 0 & "', '" & 0 & "', '" & 0 & "', '" & 0 & "', '" & 0 & "', '" & runningCom & "', '" & runningObl & "','" & runningCost & "', '" & runningPlanned & "', '" & runningBase & "')")
                   End If
                Next x
            End If
                  
            For x = 1 To 12
                full(x) = 0
            Next x
            
            If rs![Period Number] > 1 Then
                For x = 1 To rs![Period Number] - 1
                    db.Execute ("INSERT INTO tblOnePager ([Project Code], Benefitor, [Op Plan Description], [Period Number], [FY Commitment], [FY Obligation], Cost, Planned, Base, runningCom, runningObl, RunningCost, RunningPlanned, RunningBase ) VALUES ( '" & rs![Project Code] & "', '" & rs!benefitor & "', '" & rs![Op Plan Description] & "', '" & x & "', '" & 0 & "', '" & 0 & "', '" & 0 & "', '" & 0 & "', '" & 0 & "',  '0',  '0', '0', '0', '0')")
                    full(x) = 1
                Next x
            End If
            
            full(rs![Period Number]) = 1
            
            projectcode = rs![Project Code]
            benefitor = rs!benefitor
            opplan = rs![Op Plan Description]
            period = rs![Period Number]
            runningCom = curCom
            runningObl = curObl
            runningCost = curCost
            runningPlanned = curPlanned
            runningBase = curBase
       
            
        End If
        
        db.Execute ("INSERT INTO tblOnePager ([Project Code], Benefitor, [Op Plan Description], [Period Number], [FY Commitment], [FY Obligation], Cost, Planned, Base, runningCom, runningObl, RunningCost, RunningPlanned, RunningBase) VALUES ( '" & rs![Project Code] & "', '" & rs!benefitor & "', '" & rs![Op Plan Description] & "', '" & rs![Period Number] & "', '" & curCom & "', '" & curObl & "', '" & curCost & "', '" & curPlanned & "', '" & curBase & "', '" & runningCom & "', '" & runningObl & "', '" & runningCost & "', '" & runningPlanned & "', '" & runningBase & "')")
        rs.MoveNext
    Loop
    
'   Adds message box telling user the process has been completed
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Funding Data Complete!"    ' Define message.
Style = vbOKOnly + vbInformation + vbDefaultButton2    ' Define buttons.
Title = " "    ' Define title.
Help = "DEMO.HLP"    ' Define Help file.
Ctxt = 1000    ' Define topic
        ' context.
        ' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then    ' User chose Yes.
    MyString = "Yes"    ' Perform some action.
Else    ' User chose No.
    MyString = "No"    ' Perform some action.
End If

End Function

=(sumif(productlist;A12;offset(productlist;0;5))))

name of the product (computer A)

product list is the name of a column in sheet 2
(5 is the month number by the way)

formula looks for a12 in sheet 2 then gives 5th value near product list column

question
i want to find the cumulative sum 1 to 5 (for example jan to april)
how can i do

thanks in advance

In Excel, I am tracking the GPA of fraternity and sorority members to view their eligibility to be a part of the group. I am looking to somehow do a live tracking to add a semester GPA to the spreadsheet and have it automatically update the cumulative GPA based on the 4.0 scale. Can someone please help me out here?! Thanks!

I start out a workweek with one tab for Monday's date. Each day I duplicate the tab and change the numbers. Then I start all over the next week. I would like to create a formula on Monday's worksheet that will sum up one cell. For instance, cell C22 would contain a formula that will automatically give the sum of cell B22 for the current tab and all previous tabs...no matter how many there are in the workbook. This would then give me the totals as I copy the worksheet day by day. Possible? Or is this just confusing?