Excel & Statistics 45: Z-Score (# Standard Deviations From Mean)

I have a line of code that inserts a formula based upon a changing array of values:

Code:

Worksheets("Graph Data").Range("H2") = "=STDEV('" & BHP & "'!" & Ananumber & "$4:" & Ananumber & "$" & totals + 4 & ")*4"

This sets up the cell to calculate the standard deviation, however, I am looking to calculate to _4_ standard devations.

Is there a way for excel to calculate to 4 standard deviations, or am I looking at quite a long and complicated piece of code?

Cheers for your help

Nathan

I have about 40 points that I wish to graph. I used these points to get a
logarithmic trendline. Now I want to graph one standard deviation above and
one below. How do I do that.

Hey guys im trying to use the standard deviations that i have calcualted inorder to put some error bars on a graph that i have made, but its not wokring... please helP! thanks in advance!

I have a sheet of data in which Column A contains a list of sales agents names and Column B contains their results against their KPIs. All the data is terribly skewed due to new metrics so I have been tasked with attributing 'ranking band' to their performance, i.e. very low, low, medium, high, very high.

I have calculated the average and standard deviation for the list of data, but now I need to calculate which ranges will fall under which ranking band, e.g. very low is less -18%, low is greater than 18% but less than 80%, etc.

I also need to come up with a nested if statement next to each of the sales agents so it returns what ranking brand they are on based on their performance.

Below is a link with the sample data so hopefully you guys will get an idea of what Im trying to achieve.

Sample Sheet Link

Any help is greatly appreciated.

Thanks.

In the attached worksheet, we track thickness of a variety of products and record them based upon lot number. I am trying to perform statistical analysis of those products and plot the averages.

Our products are in roll form. We cut to specific widths and lengths from these "master" rolls. We take 3 measurements across the width and several times throughout the roll length. We can have multiple rolls making up 1 lot and due to demand switch between products and try to use FIFO but that doesn't always work.

I have keying in historical data and realized my standard deviation is way off but I don't know why.

I would appreciate some guidance on the formula.

Hi everyone,

I have a conditional formatting problem.

The problem is that Excel always stop when it found a condition being "true".

I have table with a range of percentages and a "standard percentage value"
I now want to highlight all percentages into three traffic light colours depending on whether they a
1. 80% greater or less than the standard value (RED)
2. 50% greater or less than the standard value (ORANGE)
3. If else then they are ok (GREEN).
in addition it would be nice to have those boxes which display 0 in blue.

I anyone could post a formula for that, that would be great.

Thanks everyone!

Hi,

I have a series of data on a sheet and want to on a summary page have the total #, Average and Standard Deviations of a selected date range. First two no problem (sumproduct, averageifs did the trick) but wondering how I do the STDEV?

Any help would be great.
Data sheet is set up as follows:
Start date = b5
End date = b6

Data sheet has the dates on row 3 and the data I want to get the STDEV on row 17

Thanks.

This is probably a no-brainer for most of you, but I've been working on it all day!

Lets say I have a data set in column A. In column B, I would like to show exactly how many SDs above or below the mean each datapoint is.

Example (Made up numbers, not checking accuracy of Sample SDs)

Column A (Data)___________Column B (Variance from Mean)
234____________________________ 0.32
34____________________________ -1.43
456 ____________________________ .49
255____________________________ .38
3356___________________________ 1.942
33_____________________________ -1.3
6______________________________ -2.1
3445____________________________ 2.4

Hi all,

I want to make a bar chart using the data average as a single bar of data (a single value) and then use a different value to display the standard deviation as an error (both + and -) overlapping the same bar (the average). How do I do this in excel 2007?

thanks ,

rob

Hi all,
I know this is a excel board and not a math board but I'm not sure where to ask this and everyone was very helpful on last question. Anyhow, I figure you guys have run into this before dealing with excel...

I am trying to calculate the effect a basketball players FG% on his teams FG%. The problem is that I need the players effect in standard deviation if possible yet am not sure how to incorporate weight (how many times they shoot). I have the data for shots made and attempted for each player on the team. I tried all last night dealing with different variations of standard deviation to get something accurate but think I've failed so far.

Thanks,
Ori

Just to clarify, I know how to use stdev well, its just I dont know how to do this specifically.