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!

## Related Results

### How to calculate a median from date selected data

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.

### How to Use Left, Right, Mid, and Len Functions in Visual Bas

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

### How do I plot mean and standard deviation in excel

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?

### Calculating a z-score and standardized index within 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

### Median, Average, and Standard Deviation from large set of data

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???

### Average With Standard Deviation In Pivot Graph/Chart

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
If wsExists(wksPivotTable, wkbNow) Then wkbNow.Sheets(wksPivotTable).Delete
On Error Resume Next
wkbNow.Charts(wksPivotChart).Delete
On Error Goto 0
'---------------------------------------------------------
'---------------------------------------------------------
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
'-------------------------------------------------
'-------------------------------------------------
For Each g In arrRow
With .PivotFields(CStr(g))
.Orientation = xlRowField
.Position = 1
End With
Next g
'-------------------------------------------------
'-------------------------------------------------
For Each f In arrColumn
With .PivotFields(CStr(f))
.Orientation = xlColumnField
.Position = 1
End With
Next f
'-------------------------------------------------
'-------------------------------------------------
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
'---------------------------------------------------------------
'---------------------------------------------------------------
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

```

### How to calculate average time

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.

Regards,

Shane

### How to calculate dates that are 30 & 60 days in the future...

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!!!????

### How to calculate certain cells on two different worksheets in Excell using VB Macro?

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

### How to calculate £ in a certain data range?

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.

### Very urgent How to calculate mean excluding few numbers

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.

### Mean and Standard Deviation

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.

### How to calculate the mean for one set of data if its within the accepted difference?

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

### How to calculate date and time difference with odd months?

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.

### Plotting means and standard deviations I already have

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

### Simulate values for a given Mean and Standard deviation

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.

### How to eliminate #N/A and 0

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.

Greetings and thanx from holland.

### Using a formula to calculate Last 10 Games and Win/Loss Streak

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.

### How to calculate what was paid and what is owed

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.

### How to calculate 2 standard deviation?

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

### How to calculate the Value of "X" if resulted Poisson value is known?

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 = ?

### How to calculate length of stay

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)

### Data selection and graph presentation

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!!