Free Microsoft Excel 2013 Quick Reference

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!

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!

- How to calculate a median from date selected data
- How to Use Left, Right, Mid, and Len Functions in Visual Bas
- How do I plot mean and standard deviation in excel
- Calculating a z-score and standardized index within Excel
- Median, Average, and Standard Deviation from large set of data
- Average With Standard Deviation In Pivot Graph/Chart
- How to calculate average time
- How to calculate dates that are 30 & 60 days in the future...
- How to calculate certain cells on two different worksheets in Excell using VB Macro?
- Very urgent How to calculate mean excluding few numbers
- Mean and Standard Deviation
- How to calculate the mean for one set of data if its within the accepted difference?
- How to calculate date and time difference with odd months?
- Plotting means and standard deviations I already have
- Simulate values for a given Mean and Standard deviation
- How to eliminate #N/A and 0
- Using a formula to calculate Last 10 Games and Win/Loss Streak
- How to calculate what was paid and what is owed
- How to calculate 2 standard deviation?
- How to calculate the Value of "X" if resulted Poisson value is known?
- How to calculate length of stay
- Data selection and graph presentation
- How to Calculate Textbox's Please Help!

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.

bar graph. How do I do this in excel?

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

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

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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.

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.

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

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.

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

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.

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.

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

function only gives 1 standard deviation.

Thanks,

Li

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

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:How to calculate the customer count today based on checked in and not checked out yet ( column B is empty)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 10If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in advance.

Thanks everyone

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