Calculate STDEV For Mid-95% Of Data

Aug 25, 2009

I have a worksheet containing data in columns. For each column, I would like to calculate the standard deviation of the mid-95% of the values i.e. 2.5th percentile to 97.5th percentile (to reduce the effect of "outliers").

So first of all I calculate the values of the 2.5th and 97.5th percentiles:

A102 (2.5th percentile) = PERCENTILE(A1:A100,0.025)
A103 (97.5th percentile) = PERCENTILE(A1:A100,0.975)

Then using an array formula shamelessly plagiarized from this very website, I calculate the standard deviation of the values that lie in that range:

{=STDEV(IF((A1:A100>=A102)*(A1:A100

View 9 Replies


ADVERTISEMENT

Stdev Using Most Recent Data From List

Aug 2, 2006

If I have 50 data points sorted in order (oldest --> newest), how do I do a stdev function that will only pull the 24 most recent points?

Essentially, I have 14 columns with 50 data points each. At the end of the list (or maybe beginning - under the column header) I want to do a stdev calculation referring to the most recent 24 points and a second for the most recent 36 points.

View 4 Replies View Related

Unexpected Result Using STDEV

Jul 21, 2009

I'm using the following formula to calculate the % relative standard deviation of 5 values:

ROUND((STDEV(C8:C12)/AVERAGE(C8:C12)*100),6)

where C8 through C12 are all 3.48.

I was surprised to see the result of 0.000002, and not 0.000000. This formula seems to be accurate to 6 decimal places when using 5 values that are not all the same. When they are the same, I get this small discrepancy in the 6th decimal place. I attempted to troubleshoot by inspecting C8 through C12. The values were typed in as seen above (3.48) and the cell formatting is set to 2 decimals in each of these cells, so the cells themselves shouldn't be contributing to the issue.

View 9 Replies View Related

Calculating Stdev When Values Include #n/a

Sep 14, 2009

I have a set of values. #N/A413636363632. The #n/a is there on purpose for charting reasons. How do I calculate the stdev of that range? i.e. ignoring the error?

View 2 Replies View Related

Excluding Cells With Errors Or Zeroes Before Doing STDEV

Mar 15, 2013

I'd like to calculate the standard deviation and mean of a whole load of data. Unfortunately it has some errors, FALSE values, blank cells and zeroes I'd like to exclude. Calculating the average is no problem as the AVERAGEIF function works fine, but there is no equivalent for STDEV. The (array) formula

STDEV(IFERROR(E29:E32,FALSE))

ignores the cells with errors and calculates the SD, and the (array) formula

STDEV(IF(E29:E32>0,E29:E32,FALSE))

ignores the cells with a value of 0 in them and calculates SD.

How can I combine the two into one formula?

View 3 Replies View Related

STDEV With Multiple Criteria With Blank Cells

Dec 24, 2013

I have trouble with calculating standard error with multiple criteria. Below is the formula I used:

=STDEV(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,M2:M25))))/SQRT(COUNTIFS($J$2:$J$25,"A",$K$2:$K$25,"B",$L$2:$L$25,1,M2:M25,""))

The problem is the blank cells in the range that I am calculating (M2:M25). Everything is alright if there were no blank cells in M2:M25. If there were any blank cells, however, the STDEV was wrong (the countifs part was correct). It treated the blank cells as zero.

View 6 Replies View Related

Floating Range For Excel Functions - Calculating Stdev And Average

Jan 22, 2014

I need to calculate a Stdev and Average based on a specific range. However, my range is not constant in the time. For instance, today I may need to calculate these functions based on 30 numbers, but tomorrow on 25 or 15 or 50...

Is there a way to automate this process by changing the number of data points to include in the range in one single cell and avoid manually adjusting the range in accordance with needed data points?

In other words, I would like to change the value, let's say in Cell A1 from 30 to 50 and then Excel would re-calculate the StDev and Average based on 50 data points and not 30. Is that possible?

View 12 Replies View Related

How To Calc Stdev But To Exclude 0's. Prefer No Array Cntrl + Shift + Enter Calcs

Dec 12, 2008

I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.

View 8 Replies View Related

MEDIAN, STDEV Of A Range IF Another Range = Specific Value

Apr 26, 2006

I want to get the MEDIAN of the values in F2:Q107 only if the value in D2:D107 = D111, how do I do this. I want to do the same thing with MEDIAN.

I don't want to use a pivot table if I can avoid it.

View 6 Replies View Related

VBA To Autofill Formula Over Column Data Calculate And Replace Original Data

Nov 14, 2008

Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.

I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008

All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'

Sub Create_formula_result()

Dim Limit As Long
Dim r As range
Set r = range("A1")

r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))

End Sub

View 9 Replies View Related

Getting Data In Adjacent Cell To Calculate Data

Dec 17, 2012

I want the user the be able to pick a number. This number corresponds to a table value. The formula needs to take the value in the adjacent cell from the picked number and run a calculation on it.

For example, if the user picks 7, it will select the adjacent value to 7 in a table for use in a calculation in another cell.

I attached an example : example_001.xlsx‎

View 3 Replies View Related

How To Ignore Data And Calculate Other Data In Same Cell

Sep 20, 2013

I have a cell with two parts of data which looks like this 21A (5.5) now 21A is a location plot number and the (5.5) is the area in rods of the plot.

Now I need to calculate the cost of this plot by multiplying the number of rods by the cost per rods, so is there a way of just using the number in the brackets in a sum and ignoring the rest of the information, now I know I could just make another cell with the 5.5 bit in it but it makes the spread sheet too complicated and wide

View 14 Replies View Related

Calculate A X Value From Given Y Value Based On Data

Dec 29, 2006

I have few x values
I have few y values corresponding to the x values.


How to calculate a x value from given y value based on the above data. I tried linear iterpolation, I am not getting good results.

View 14 Replies View Related

Calculate If Data Matches

Feb 14, 2013

In the attached excel sheet see the sample example tables 1.1.1 and 1.1.2. I have to use it for official purpose and my manger has given this task to complete it. I cannot even compare the forumlas and make it same for all the tables available.

How can I attach the excel file...

View 4 Replies View Related

Calculate From Data Validation?

Apr 29, 2013

I'm trying to produce a simple caluclation without creating multiple "IF" Statements.

In Col C I have a Simple Data Validation which chooses currency ( this table is Simple, Currency Code, Conversion Rate.

In Col D I enter the Value of the request.

Col E is where I want the result showing the USD value from the value I entered in Col D based on the Currency I chose in the Data Validation.

So in my sheet I have Currency BHD, total 20000, Value in USD is Col D/lookup Col C in data validation table.

View 3 Replies View Related

Formula To Calculate Data

Dec 3, 2013

East
Item #
Cases in East
Dropped 1
Pending
Wave 1 - Qty
Dropped 2
Wave - 2 Qty
Dropped 3

[Code]...

I have above data where in I need to formula to calculate the 428 in dropped 1 should get calculated in Dropped 2 & dropped 3 as per the plan in wave 1 & wave 2

View 3 Replies View Related

Calculate An Average As Data Is Entered?

Aug 26, 2009

I'm using Excel 2003. I have about 190 rows that I use on any given day to enter start times & end times. I calculate the difference in Column E. Is there a formula that will calculate the average time as I enter them in the rows? Some days may have only 100 entries, other days may have as many as 190. I don't want to keep adjusting the average formula for column E.

View 2 Replies View Related

Formula To Calculate Value After Doing Data Match?

Dec 13, 2012

Refer the attached work sheet and any formula to calculate the values once the criteria is met.

To calculate % after multiple look ups.xls‎

View 7 Replies View Related

Getting Excel To Calculate All Combinations Of A Set Of Data

Nov 4, 2005

I've found myself having to use a spreadsheet to calculate some acoustics problems, (problematic frequencies, called modes, in orders ranging from 0,1,2,3....).

Ideally what I want to be able to do is simply to get Excell to calculate all the possible combinations of these modes.

A combination could be:
000
011
101
110
111
012
143
etc.

Obviously this is a nightmare to input by hand, is there any kind of function I can use to help with this process?

Also these frequencies only need to be calculated up to a certain point, so is it possible I could also instruct the cell to only display a value if it is less than a value in another cell?

View 10 Replies View Related

Calculate Sum Where Column Contains Intermittent Data?

Jun 13, 2014

K
L
M
N

L

L
-2.33

View 6 Replies View Related

Calculate Cycle Length Of Data Set

Feb 19, 2008

I have a data set that contains a repeating cycle. I want excel to find the first and second occurrence of the data crossing the zero axis and then report the time between the zero crossings. The data is not purely in ascending or descending order and often just changes from positive to negative or negative to positive without hitting the actual 0 point.

View 3 Replies View Related

Calculate Data Based On Laid Down Criteria?

Jul 26, 2013

I am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:

Cab Type in cell () Indica

If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.

For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)

If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800

For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2)
Cab Type in cell () Indego

If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.

For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200

For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)

Ther are about 5 different types of cabs in the sheet.

Cab Type Provided
Total KM run
Basic Amount
Extra km

What I want to achieve is as under:

1. If value of cell A1 = Indica and value of cell B2 is entered as 40

Then the values of cells (C2:e2) should populate automatically as 400, 0, 400

2. If value of cell A3 = Indica and value of cell B3 is entered as 40

Then the values of cells (C3:e3) should populate automatically as 400, 40, 440

View 8 Replies View Related

Calculate Monthly Sales Based On Data Given?

Jun 9, 2014

How to calculate the monthly sales based on the data given ?

View 4 Replies View Related

Data Sorting Query - Get Excel To Calculate?

Feb 10, 2013

I am a teacher and have a recurring problem when trying to sort data in excel. The problem involves national curriculum sub levels. For those not familiar with these levels, there is a main level then 3 sub levels. The problem is that they are counter intuative to the way excel sorts data. The sub levels work like this:

4a - highest
4b
4c
3a
3b
3c
2a
2b
2c - lowest

There are lower and higher levels, but you get the idea. If I have a set of pupil data that I want to sort into decending order, I find it difficult in Excel. E.g.

Jacob2c
Mary3b
John2a
Sally2b
Sarah2b
Joe3a
Bill3a

If I manually sort the above data into descending order of national curriculum level it will look like this:

Joe3a
Bill3a
Mary3b
John2a
Sally2b
Sarah2b
Jacob2c

but if I try to get excel to do it, obviously excel just treat the levels as alpha numeric and sorts them like this:

Mary3b
Joe3a
Bill3a
Jacob2c
Sally2b
Sarah2b
John2a

I know there must be an easy way involving creating a custom list, but I don't know how to do it.

Problem 2

A related query involves finding a way to get excel to complete the data in the Sub levels progress required column below:

NameLevelTargetSub levels progress required
Mary3b4a
Joe3a4a
Bill3a4a
Jacob2c2a
Sally2b3c
Sarah2b3c
John2a3a

At the moment I manually complete this. E.g. for Mary the Sub levels progress required would be 4, as there are 4 sub levels between 3b and 4a. For Joe it would be 3 as there are 3 sub levels between 3a and 4a, etc. Is there a quick way to get excel to calculate this.

View 6 Replies View Related

How To Use Buttons To Lookup Data Then Calculate Output

May 1, 2013

I am struggling to begin designing a program to use for quoting sheet metal parts. We buy three types of material in 9 different thickness. The material is purchased by weight but I sell it by square foot.

I tried a few training exercises using macros but I dont believe macros is the proper function to achieve my output.

View 6 Replies View Related

Vacation Data - Calculate Difference Between Two Dates

Mar 5, 2012

I have a vacation data of my company. I want to calculate difference between two dates (relieving date & rejoining date) after excluding holiday(which I will decide as per my company policy) also note that now i m in gulf country hence here Friday is weekly off not Sunday.

View 9 Replies View Related

Calculate Average $/HR By Pulling Data From Two Workbooks

Mar 15, 2013

I have two excel files that I am trying to gather information from to calculate the average $/hr earned by employees. The first file, which is currently called "Book 1" is generated by a different VBA code, the other "Employee Hour Output" is generated by our time clock program. What I am trying to do is have the script run from the PERSONAL workbook and look in the Employee Hour Output. In column B there are Employee names, for each employee that worked, there is a list of invoice numbers located in column E.

The code needs to see which invoice numbers each employee worked on then compare that to the $/HR earned from that job in the Book1 workbook. Then based on all the jobs worked, calculate the average $/HR that employee earned in the given time period. This information needs to be displayed with the employee name and $/HR in the first available row of columns H and I of the Book1 workbook.

View 1 Replies View Related

Calculate Difference Between 2 Rows In Data Table?

Aug 7, 2013

I have a data table with incidents in which one column is equipment number and one is data of occurrence. I now need to calculate how long ago the same equipment number occurred.

View 2 Replies View Related

Paste And Calculate Odds Data Into Excel

Aug 19, 2013

Basically all I want to do is paste some odds data in to excel and then get it to just calculate the odds for me example below

9/4 Exotic Isle, 5/2 Greenery, 5/2 Port Alfred, 6/1 Reqaaba, 12/1 Somoud

I know I can use text to columns using the deliminator to split them in cells however I just want to take the odds

So Exotic Isle would be 2.25 (9/4) Greenery would be 2.5 (5/2) and so on

I've tried using the left function basically all sorts.

View 3 Replies View Related

Calculate Data Input And Assign This To Cell

Aug 6, 2008

Is is possible to create a calc for simple data input and assign this to cell U31 so that everytime user selects this cell then the calc pops up.

I didnt want to use an add-in in case other users don't have this add in on their computer.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved