Free Microsoft Excel 2013 Quick Reference

How to calculate Mean, Median, Mode and standard deviation in group data?

Hello,

I am trying to create a worksheet capable of calculating the turnaround processing time for emails in a 14 days time frame.

I am wondering if it is possible to create a dynamic excel worksheet which can calculate Mean, Median, Mode and standard deviation within a range of data as Column B listed below changes every forthnightly.

Sample data below:

Column A Column B
Number of days Email
O – 1 600
2 - 3 700
4 - 5 100
6 - 7 50
7 - 14 30

- First row: 600 emails took a day to complete
- Last row: 30 emails took between 7 - 14 days to complete

1. To find the average processing time
2. To find the median processing time in days
3. What is the standard deviation (in days)

Would greatly appreciate if someone could help.

I apologise if I was unable to articulate the whole scenario clearly.

Many Thanks!


Post your answer or comment

comments powered by Disqus
I would appreciate any suggestions for the following problem:

I have a large table of transaction data where each row includes a date and a price. From this data, I would like to form a summary table where each row summarizes the transactions for a month. Each row of the summary table should contain a column for the month summarized, a count of the number of transactions in that month, the average of the transaction amounts for that month, and the median of the transaction amounts for that month.

I can form the summary count and the transaction average with array formulas, but I can’t figure out how to create the median. Note that in this case, data needs to be selected from the original table, then sorted, and then the median selected. The Excel built in Median function has a limit of 30 arguments, so I’m not sure if I can use that or not.

Thanks in advance for any suggestions for how to calculate the median in this situation.

XL: How to Use Left, Right, Mid, and Len Functions in Visual Basic

I want to take a data series and plot the mean and standard deviation on a
bar graph. How do I do this in excel?

Hi,

I have a data table with 61 groups which has been aggregated to give me a count of the no. of occurances of each group and an average house value for each group.

I have calculated the index score for each against the mean house value but would like to calculate a z-score and standardized mean in order to take into account the count associated with each group (i.e. the group where the count is only 1 is less statistically valid then the group with a count of 50)

Any ideas with a formula to use or do I need to give anymore information?

Thanks

Martin

Hi all,

I'm stumped. I have a very large set of data (over 200.000 records in
MSAccess DB) that I'm analyzing using Pivot Tables. Average and Standard
deviation functions are "built-in" so no problem there... now, how can I
calculate the median???

Please help!
Thanks in Advance

I've just discovered how extraordinarily usefull pivot-tables can be. But I am still having problems with its pivot-charts, especially in VBA.

My problem: In particular, I have a pivot-table with all "amount of data-sets", "average" and "Standard Deviation" of my data, which I'd like to visualise in a pivot-chart. I would like to have the pivot-chart show a xlColumnClustered Chart with the "average" and the "Standard Deviation" (as y-errors of the average!!!) - and if possible the "amount of data-sets" in/on/next to the correspondent column. I don't think that is something new or unusual, but I simply haven't found out, how to do so at all (in VBA).

Reached so far: What I have been able to encode is the establishment of the pivot-table and the chart (see code below). But I cannot get the chart to show the "Standard Deviation" as a y-error of the "average"-values! (Also, but this is less important, I neither get to show the "amount of data" ON the correspondent column.) For a usual chart, I do know how to add the y-error ... but for the pivot-chart, I don't know how to do so, cause relying on defined data-ranges for the chart, would not be a good idea in case somebody simply rearranges the pivot-chart, I fear.

Below you find the code, I have been able to make up so far, I appreciate any help,
Andy

[EDIT] I know, there is the button to add a "y-error" where you can choose from. But it does not contain the standard deviation (sample), whereas this is exactly what I can calculate in the table ... but I dunno how to make the standard deviation (sample) appear as the y-error in my chart.


	VB:
	
, strNFormat) 
     '
     ' This macro creates a pivot-table and a pivot chart based on the data in rPivot.
     ' wks: worksheet of rPivot
     ' rPivot: range of data for pivot-table
     ' strTblNme: future name of the pivot-table
     ' arrData: array field contains four infos about every field
     ' arrRow: array field, contains info about the row field
     ' arrColumn: array field, contains info about the column field
     ' strNFormat: (mostly = "0.00")
     '-------------------------------------------------
    Dim wkbNow As Workbook 
    Dim rStart, rUsed As Range 
    Dim myPiv As PivotTable 
    Dim pTable As Worksheet 
    Dim pChart As Chart 
    Dim b As PivotField 
    Dim i As Integer 
    Dim e, f, g As Variant 
     '-------------------------------------------------
     
     '---------------------------------------------------------
     ' Clean up and delete old pivotTable and pivotChart
     '---------------------------------------------------------
    Set wkbNow = ActiveWorkbook 
    Application.DisplayAlerts = False 
    If wsExists(wksPivotTable, wkbNow) Then wkbNow.Sheets(wksPivotTable).Delete 
    On Error Resume Next 
    wkbNow.Charts(wksPivotChart).Delete 
    On Error Goto 0 
    Application.DisplayAlerts = True 
     '---------------------------------------------------------
     ' Add Pivottable
     '---------------------------------------------------------
    wkbNow.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=wksIndexName & "!" &
rPivot.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable TableDestination:="", TableName:=strTblNme,
DefaultVersion:=xlPivotTableVersion10 
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
    Set pTable = ActiveSheet 
    pTable.name = wksPivotTable 
    Set rStart = pTable.Cells(3, 1) 
    Set myPiv = pTable.PivotTables(strTblNme) 
     '---------------------------------------------------------
     ' Fill Pivottable
     '---------------------------------------------------------
    With myPiv 
         '-------------------------------------------------
         'Add RowField Variables
         '-------------------------------------------------
        For Each g In arrRow 
            With .PivotFields(CStr(g)) 
                .Orientation = xlRowField 
                .Position = 1 
            End With 
        Next g 
         '-------------------------------------------------
         'Add ColumnField Variables
         '-------------------------------------------------
        For Each f In arrColumn 
            With .PivotFields(CStr(f)) 
                .Orientation = xlColumnField 
                .Position = 1 
            End With 
        Next f 
         '-------------------------------------------------
         'Add DataFieldContent
         '-------------------------------------------------
        For Each e In arrData 
            If e(1) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "Data-sets of " & CStr(e(0)),
xlCount 
            If e(2) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "Mean of " & CStr(e(0)),
xlAverage 
            If e(3) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "StDev " & CStr(e(0)), xlStDev 
        Next e 
         '-------------------------------------------------
         'Set Number format for every data-field
         '-------------------------------------------------
        pTable.UsedRange.NumberFormat = strNFormat 
    End With 
     '---------------------------------------------------------------
     ' Add Pivot Chart
     '---------------------------------------------------------------
    Set pChart = Charts.Add 
    With pChart 
        .SetSourceData Source:=pTable.Range("B4") 
        .Location Where:=xlLocationAsNewSheet 
        .ChartType = xlColumnClustered 'chart type
        .name = wksPivotChart 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I would like to know how to calculate the average time. Attached please find a sample data. Column "Total Time taken" conatins the time for each and every transaction.

I would like to know the average time. Please help..

Regards,

Shane

I need to know how to calculate a date 30 days and 60 days in the future.

I will provide an initial date in one cell and want the next cell to contain the date of 30 days in the future and the next cell to be 60 days in the future.

might sound easy to just push out one and two months in the future without a formula, but I want to streamline the data input process and have the computer generate as much as possible....

can anyone help!!!????

Dear All,

I am a new in macro scripting. I have question on how to calculate the certain cells between 2 different worksheets based on the certain calculation parameters and the comparison result (the report) is put into the new worksheet on the same workbooks.

1) Worksheet1 contains: DEPTH, G1, C1, N1, D1, S1, SR1, MR1, DR1
2) Worksheet2 contains: DEPTH, G2, C2, N2, D2, S2, SR2, MR2, DR2

Expected result should be put in Worksheet3: DEPTH, G1, G2, calculation(G1 and G2), CI1, C2, calculation(C1 and C2), so on until the last columns.
(Please find example on attached file "Test_Data.xls)

What I have done is to create a template to be filled in by copy and paste DEPTH, G1, G2 on worksheet1, DEPTH, C1, C2 on worksheet2, so on (attached file "Template_QC.xls"). Then I create a calculation macro for each of those worksheets separately to calculate the difference and color them . The calculation macro that I used is as follows:

Public Sub GTemplate()
  Dim LastRow As Long, iRow As Long
  Dim diff As Double
  LastRow = Range("A1:D1").SpecialCells(xlLastCell).Row
  For iRow = 2 To LastRow
    Cells(iRow, "D").Value = Cells(iRow, "C").Value - Cells(iRow, "B").Value
    If Cells(iRow, "D").Value >= 2 Or Cells(iRow, "D").Value <= -2 Then
        Range(Cells(iRow, "D"), Cells(iRow, "D")).Select
        Selection.Interior.ColorIndex = 3
    Else
        Range(Cells(iRow, "D"), Cells(iRow, "D")).Select
        Selection.Interior.ColorIndex = 43
    End If
  Next iRow
End Sub
That macro above is applied to all worksheets, except the calculation function and number mentioned in "If Cells(iRow, "D").Value >= 2 Or Cells(iRow, "D").Value <= -2" are different from one worksheet to the other

In other words it is still a bit manual works. Now, how can I automatically calculate my 2 worksheets based on my calculation parameters above and put the resulted report in worksheet3 using VB Macro in my workbook?

Regards,
WiD

Hi guys,

I am creating a spreadsheet where I am trying to get statistics for a month as well as totals. I have managed now to get totals per month for activities, now I just need to work out the currency parts of it.

Attached is the spreadsheet that I have made so that you can get a clear understanding of it.
All i need to work out is how to calculate the GROSS, NET and TAX of a given date range?

Thanks.

hi everyone
my problem is that I have data in column A (600 values). i need to calculate the mean of 600 values. I can simply type the function of average to calculate the mean. But my problem is among the 600 values, many are 0s. and i do not want zeros to be included. So how do i calculate the mean of 600 numbers( some of them being zero) without including the zero.
for eg. 1,2,3,4,5,6,0,8,9,0,7,0,7,7,9,0,6 (the average would be sum of all 17 numbers divided by 17)
my problem is i want the mean of only those values which are greater than 0.

Is there a formula I can use to calculate the mean and standard deviation of a selection of data? I have tried a few different ones and they don't seem to be working correctly.

Dear,

I have 2 columns Item and price

A B E
1 Item price Mean
2 A 12 9.6
3 A+ 10
4 A++ 11
5 A+++ 5
6 A++++ 10
...

lets say the accepted difference is 3

i want to calculate the average price for the cells in column B for those values that they are within the accepted difference from the mean which it is 9.6 in the example above.

so, the row number five where B5 = 5 should not be in the average price ( i want to exclude this value)

Thanks

Hi to all, ok I basically have 5 columns
D E F G H

Collumns D and F are Start and Finish dates written as (MM/dd/year), and E and G Start and finish times. While H is the result table in which I express the difference in fix hour times

I need to calculate the difference of these periods but in hours

=IF(MONTH(H15)=MONTH(F15),((DAY(H15)-DAY(F15))*24)+(HOUR(I15)+MINUTE(I15)/60)-(HOUR(G15)+MINUTE(G15)/60),(((DAY(H15)+31)-DAY(F15))*24)+(HOUR(I15)+MINUTE(I15)/60)-(HOUR(G15)+MINUTE(G15)/60))

The formula above works, but as I put a dates between months it provides me a negative hour.... how can I fix this.

I have a chart that I just cannot figure out how to graph.
It is some survey results and the mean and standard deviation of each
question.
I want the horizontal axis to list 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5,
and 5.0.
I want the vertical axis to list the question topic: eg, a, b, c, d, e, f,
g, h, and i.
For each question, I want to show the mean answer and the standard deviation
on a bar, with a point for the mean and the endpoints being the +/- standard
deviation.
Examples of the data:
Question a - Mean = 3.6875 Std. Dev. = .59387
Question b - Mean = 3.8958 Std. Dev. = .67810
Question c - Mean = 3.1875 Std. Dev. = .65730
etc.

How can I set this up to create a chart like what I need, where I basically
have a bar that stretches plus or minus the standard deviation, with the mean
in the center?
Thanks,
Kim

I want to simulate values for a given mean and standard deviation. wat
is the procedure to do this? is there any free excels addins available
to do this.
For example, My portfolio return(mean) is 12% and Risk(SD) is 18% . Now
I want to simulate values for next 30-40 years Returns. How to do this?
is there any free add-ins that wud generate these yearly returns if we
provide the population mean and standard deviation.

Hi,

i'm currently working on a database, where I want to eliminate #N/A and 0, in order to calculate averages, medians, kurtosis and skewness.

I understand how to eliminate #N/A into " ", but so far I'm stuck at combining the formula where I can also eliminate 0 and change it also into " ".

=IF(ISNA(VLOOKUP($$$;FALSE));"";VLOOKUP($$$;FALSE)) This helps me to eliminate #N/A, but not the 0.

Please help.

Greetings and thanx from holland.

I've created a workbook for the 2011–12 NHL season so I can calculate standings based on what would happen if the NHL were to change their current standings format of Win-Regulation Loss-Overtime/Shootout Loss to a basic Win-Loss format, where standings are based on Win Percentage instead of Points.

To do this, I've used a workbook of the 2011–12 schedule created by Dirk Hoag of On the Forecheck, where I can enter the results of each game in the sheet labeled "Results", and all the team's stats will be calculated automatically using formulas and reflected in 3 standings tables, on the sheets "League", "Conference" and "Division" (for the entire league standings, the standings of the two conferences, and the standings of the six divisions, respectively). I found formulas to calculate each team's stats, i.e. Wins, Losses, Home/Away Record, Goals For/Against/Difference, etc. from various places on the internet, especially here.

Attached is the workbook, but here's a sample formula, used to calculate the Home Wins for the team whose name appears in B2 on the sheet labeled "League":

=SUMPRODUCT((Results!G$2:G$1231=B2)*(Results!D$2:D$1231<=Results!E$2:E$1231)*(Results!C$2:C$1231<=Re sults!F$2:F$1231)*(Results!E$2:E$1231<>""))

(Since the season hasn't started, I've entered some hypothetical results to test the formulas.)

The only thing I can't figure out how to calculate are the columns labeled "L10" and "Streak". "L10" means the team's Win-Loss record in their last 10 games, and "Streak" is the team's current Win-Loss streak. Can anyone figure out formulas I can use to calculate these? I only need the formula to be used on the sheet named "League", I can transpose info to use them on the other two sheets. Also, I'd prefer a formula, as I'm not familiar with using VBA. Thanks in advance.

I need to calculate a loan amount(50K) at 7% of interest and in the same time put several different amounts of payments that were made in a period of 2 years.
I need to be able to see how much of the total was paid each month on that loan and how much It was still owe every single month.
Is there a formula , a way to do that instead of calculating manualy cell by cell ?

Thanks a lot.

Pls advise how to can I calculate 2 standard deviation in Excel? The StDev
function only gives 1 standard deviation.
Thanks,
Li

If some one can help me how to calculate the value of "X" if I know the resulted value of Poisson distribution. e.g
the Poisson formula in XL is like
(X,Mean,Cummulative) , if X = 10 , Mean = 6.5 and Cummulative is True the resulted Poisson distribution is 0.93 but what I want to calculate is the Value of X if I already have Poisson distribution = 0.93 , Mean = 6.5 , X = ?

Dear All,
I have two dates for check in and check out and I need to calculate length of stay.
The following example:

today() is 01/17/2011

	VB:
	
A           B                 C 
check In    check out    length of stay 
01/05/2011  01/10/2011   5                     =If(ISBLANK(B2),SUM(TODAY()-A2),SUM(B2-A2)) 
01/05/2011               12 
01/05/2011  01/15/2011   10 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How to calculate the customer count today based on checked in and not checked out yet ( column B is empty)

Thanks in advance.

Hi everyone. Yet another question for you all. Im designing a system which will require the user to enter a 3 chrater code which will then display relevent data corrisponding with the code. I have a data base which holds all the different codes and list of data relating to each code. What i want the system to do is find the data that relates to the code that is entered by the user and display this data in graph format and display all the mean, meadian, mode and standard deviation information etc. I realy dont know how to go about this and i would just like some feedback on how you think this may be done.

Thanks everyone

Hello, i am new to VBA and have been trying how to figure something out in VBA now for hours... I have created a Banking program and would like to know how to calculate a monthly fee.

Heres What it looks like:

Enter Loan Amount: (TextBox1)

Enter the Amount of Months in the loan: (Textbox2)

Enter Anual Interest Rate: (TextBox3)

BUTTON: CALCULATE MONTHLY FEE!

I am trying to display the monthly fee in Listbox3!

Thank You!!


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