i have a list of numbers in one column and an answer (yes/no) in the next colomn

i want to check the standard deviation only if they answered yes

i know there isn't an stdevif function similar to averageif in excel 2007 but could anyone recommend a clever way in solving my problem?

thanks

i want to check the standard deviation only if they answered yes

i know there isn't an stdevif function similar to averageif in excel 2007 but could anyone recommend a clever way in solving my problem?

thanks

- STDEV if with two data sets
- Parameters with STDEV (IF, AND) for blank cells
- Stdev IF...
- STDEV with some empty cells: error #DIV/0
- Continued... CSE if functions and blank cells
- Stdev function error!
- AVERAGE and STDEV functions with logic
- Stdev(If)
- AVERAGE and STDEV functions with logic
- Stdev function error!
- Standard deviation and if (FORMULA)
- STDEV based on condition in another column
- Excel 2003 STDEV and SUMIF Formulas with Multiple Criteria
- Calc RSQ and exclude zeros
- Small Problem with Array
- Product Function in VBA Code.
- Help with Formula Array
- Using Names in a macro
- SumIF and STDEVIF is there a such thing
- Calculate standard error, discounting cells with 0 value
- Standard Deviation with a condition in several tabs
- Standard Deviation of Dynamic Range
- Calc RSQ and exclude zeros
- Stdev using most recent data from list

=STDEV( IF(ISNUMBER(DataRange), IF(DataRange < D8, DataRange)))

Let me better explain I have a row of % returns and then a row of ratios what i would like to do is create a formula that would act like this: STDEVIF(ratio"

I am trying to calculate the standard deviation for Column B. I want the number skipped if the cell next to it (Column A) has a '1' written in it. The cell in Column A is either a 1 or blank. I was using this equation:

=STDEV(IF(A1:A20="",B1:B20))

However, if I only have data to row 10 then it counts 10 more cells of '0', as the cells 11-20 are blank in column B, throwing off my standard deviation. This is a template that I am filling in over time, so I don't want to manually adjust the range it is looking across as I enter data.

I thought I could use:

=STDEV(IF(AND(A1:A20="",B1:B20>0),B1:B20))

but it gives a #DIV/0! error unless all the cells in column B are filled greater than 0.

And yes, I am using CNTRL+SHIFT+ENTER to confirm the command.

Any help would be greatly appreciated! I was able to get my averaging to work, but for some reason the STDEV command is trickier.

so- what is the stdev of values in column E if the values in column A ="A"?

essentally I need stdev to function like SUMIF.

Im attaching my data.

Also, is there a function that will return the average IF. I am currently summing If, and then dividing by the countIF. is there a function that does the same thing?

Unrelated question:

what exactly does ctrl-shift-enter do?

Take the standard deviation of values in column L if the they have the same code (code matching selected cell) in column B. Divide this number by the square root of the number of observations of the code in column B, minus one.

in excel it looks like this:

=STDEV(IF($B$2:$B$13=$B2, $L$2:$L$13))/SQRT(COUNTIF($B:$B,$B2)-1)

last friday we modified my AVERAGE IF formula to include an ISNUMBER statement to account for blank cells (not zero cell values), in column L.

So with that in mind I can modify the standard deviation half of the formula to be:

=STDEV(IF($B$2:$B$13=$B2,IF(ISNUMBER($L$2:$L:$6),$L$2:$L:$6)))

so the standard deviation will be taken (divided by the appropriate number of observations) if there is a blank cell in the list... right?

Now how can I modify the last half of the formula to do the same thing. The COUNTIF is based on the number of observations of the same code in column B, but the count would have to be modified by subtracting the corresponding number of blank cells in column L.

My Data and Brain appreciate ALL YOUR HELP!

'Divide by Zero Error'. I have different data in different column and I want

to do a calculation of all the data located in different column. What is

wrong?

I am going crazy trying to determine what is wrong with my formula. It

seems simple but for some reason I cannot get it to produce and display

correctly. Here is the issue:

I am trying two calculations based upon a range of numbers that are in

a column (lets just say A2:A4). I want to compute the average and

standard deviation using the AVERAGE function in excel and the STDEV

function in excel. Here is where it gets a little tricky. If there

are any "zero" values in the data I want the formula to ignore those

values and still produce a result with the remaining relevant

information. So lets say that the data in the column looks like this:

COLUMN

A

-----------------------

ROW(2) 10

-----------------------

ROW(3) 5

-----------------------

ROW(4) 0

-----------------------

I want my AVERAGE function to take determine that the only data it will

use to take the average are in rows 2 and 3 since row 4 has a zero

value. And the same with my STDEV function. Now I believe that I

found the correct way to use the function with logical IF functions but

I keep getting a #VALUE! result in the result cell. When I look in

detail I am getting the correct result but it shows up as #VALUE! in

the cell. WHY!!!! My STDEV function looks like this:

=STDEV(IF(G2:G40,G2:G4,"")) I see the formula result of 3.536 in the

gray formula box which is correct but the cell still returns the

#VALUE!. Please help.

I have the exact same problem with the AVERAGE function. The gray

formula box displays the correct result but the cell displays the

#VALUE!. This formula looks like:

=AVERAGE(IF(D240,D24,""))

I think maybe I am just missing something very small but I cannot

figure out what. Please help.

Regards to anyone who can,

Tom

--

t-rung

------------------------------------------------------------------------

t-rung's Profile: http://www.excelforum.com/member.php...o&userid=23772

View this thread: http://www.excelforum.com/showthread...hreadid=374154

Example of Data:

Aiping 0.07

Andy 0.10

Buzz 0.11

Andy 0.12

Aiping 0.22

buzz 0.13

formula I have used: =STDEVP(IF(A1:a6="a*",B1:B6)) returns a #div/0! error if I put "aiping" of "andy" =STDEVP(IF(A1:a6="andy",B1:B6)) it will calculate each one but I am trying to get the stdevp of both aiping and andy together.

Thanks

I am going crazy trying to determine what is wrong with my formula. It seems simple but for some reason I cannot get it to produce and display correctly. Here is the issue:

I am trying two calculations based upon a range of numbers that are in a column (lets just say A2:A4). I want to compute the average and standard deviation using the AVERAGE function in excel and the STDEV function in excel. Here is where it gets a little tricky. If there are any "zero" values in the data I want the formula to ignore those values and still produce a result with the remaining relevant information. So lets say that the data in the column looks like this:

COLUMN

A

-----------------------

ROW(2) 10

-----------------------

ROW(3) 5

-----------------------

ROW(4) 0

-----------------------

I want my AVERAGE function to take determine that the only data it will use to take the average are in rows 2 and 3 since row 4 has a zero value. And the same with my STDEV function. Now I believe that I found the correct way to use the function with logical IF functions but I keep getting a #VALUE! result in the result cell. When I look in detail I am getting the correct result but it shows up as #VALUE! in the cell. WHY!!!! My STDEV function looks like this:

=STDEV(IF(G2:G4<>0,G2:G4,"")) I see the formula result of 3.536 in the gray formula box which is correct but the cell still returns the #VALUE!. Please help.

I have the exact same problem with the AVERAGE function. The gray formula box displays the correct result but the cell displays the #VALUE!. This formula looks like:

=AVERAGE(IF(D2:D4<>0,D2:D4,""))

I think maybe I am just missing something very small but I cannot figure out what. Please help.

Regards to anyone who can,

Tom

'Divide by Zero Error'. I have different data in different column and I want

to do a calculation of all the data located in different column. What is

wrong?

I am trying to calculate the standard deviation for each company, for a given year. I am using this formula but no success until now.

=STDEV(IF('WRDS data'!$M$2:$M$107352=A4&$B$3,'WRDS data'!$J$2:$J$107352))

M contains company codes and year let's say: @IBM1998 whereas J containes the values.

Any hint?

Thank you in advance.

Tried this

=STDEV((O3:O82="yes")*(C3:C82))

or

=STDEV((O3:O82="yes")*(C3:C82)+0)

or

=STDEV(IF((O3:O82="yes"),(C3:C82)))

using CSE

but all return the wrong answer...

What as I doing wrong?

Thanks a lot in advance.

J.

I am trying to get the two formulas to work below. Basically for the first one I am trying to take the stdev of all the values in column Q that have the data in column J values listed are = "1", as well as the value in O being >19 as well as <39. If J=1, and O>19 as well as O<39, I would like it to take the stdev of all the values that fit the criteria in row Q.

The second one is very similar, except I want to sum the values if they meet the criteria.

=STDEV(IF((J2:J6357="1")+(O2:O6357>="19")+(O2:O6357<="39"),Q2:Q6357)) =SUMIF(J2:J6357,(J2:J6357="1")+(O2:O6357>="19")+(O2:O6357<="39"),Q2:Q6357))Thank you so much for your help

values in the calc.. The RSQ has 2 arguments, 1st for y's and 2nd for x's.

My problem is how to do an array formula with 2 arguments.

If I wanted to calc the STDEV ( a 1 argument function ) and exclude zero's I

would enter;

{=STDEV(IF($J11:$AG110,$J11:$AG11))}

For RSQ's normal usage I would enter;

=RSQ(J11:AG11,J7:AG7)

How do I adjust the formula above to exclude zero values?

Bruce

LR = Range("A" & Rows.Count).End(xlUp).Row M = Application.WorksheetFunction.Max(Range("A:A")) With Range("M4:M" & M + 3) .FormulaArray = "=STDEV(IF(R1C1:R" & LR & "C1=RC[-2], R1C7:R" & LR & "C7))" End WithI've attached a sample workbook. I think this should be a simple fix I just can't seem to figure it out.

I have following code in VBA...

'Downside Deviation Range("DDevFund").Formula = "=stdev(if(" & RetRng & "<0," & RetRng & ",0))*sqrt(260)"This returns #Value. If i then click in cell and do CTRL+ALT+RETURN it populates with the figure I need.

As it only works if it is a product funciton, the formula in Excel would look like...

{=STDEV(IF($Y$23:$Y$521<0,$Y$23:$Y$521,0))*SQRT(260)}

I know in a normal formula you would do CTRL+ALT+RETURN to do this.

How would i write this into my code?

Any help would be very much appreciated.

Thanks!

Dom.

=STDEV(IF(SlowAcc!AP7:AP1335=0,IF(SlowAcc!H7:H1335>0,SlowAcc!H7:H1335)),IF(MedAcc!AP7:AP1100=0,IF(Me dAcc!H7:H1100>0,MedAcc!H7:H1100)),IF(FastAcc!AP7:AP1150=0,IF(FastAcc!H7:H1150>0,FastAcc!H7:H1150)))

When I use the macro to enter the formula I get a run time error, 1004. However if I put the formula directly into the cell and use the ctrl+shift+enter it works fine. Any help with this would be greatly appreciated.

Thanks

When I run the macro on a new file, it doesn't seem to re-define tempK (for instance) as a cell range, and instead tries to use the original tempK cell range defined when I had created the macro...

How can I modify my macro so that the tempK Name is re-defined anew every time the macro is invoked ?

Sub temp_uniformity() ' ' Range("A25:I25").Select Selection.Cut Destination:=Range("B25:J25") Range("L25").Select ActiveCell.FormulaR1C1 = "tempK" Range("M25").Select ActiveCell.FormulaR1C1 = "tempC" Range("N25").Select ActiveCell.FormulaR1C1 = "tempF" Range("L26").Select ActiveCell.FormulaR1C1 = "=(RC[-8]/0.0000000000366)^0.25" Range("L26").Select Selection.AutoFill Destination:=Range("L26:L5025"), Type:=xlFillDefault Range("L26:L5025").Select ActiveWorkbook.Names.Add Name:="tempK", RefersToR1C1:= _ "='cylinderPOB-50facets-10000raysA'!R26C12:R5025C12" Range("M26").Select ActiveCell.FormulaR1C1 = "=RC[-1]-273.15" Range("M26").Select Selection.AutoFill Destination:=Range("M26:M5025"), Type:=xlFillDefault Range("M26:M5025").Select ActiveWorkbook.Names.Add Name:="tempC", RefersToR1C1:= _ "='cylinderPOB-50facets-10000raysA'!R26C13:R5025C13" Range("N26").Select ActiveCell.FormulaR1C1 = "=(RC[-2]-273.15)*1.8+32" Range("N26").Select Selection.AutoFill Destination:=Range("N26:N5025"), Type:=xlFillDefault Range("N26:N5025").Select ActiveWorkbook.Names.Add Name:="tempF", RefersToR1C1:= _ "='cylinderPOB-50facets-10000raysA'!R26C14:R5025C14" Range("L22").Select Selection.FormulaArray = "=STDEV(IF(tempK=0,"""",tempK))" Range("M22").Select Selection.FormulaArray = "=STDEV(IF(tempC=-273.15,"""",tempC))" Range("N22").Select Selection.FormulaArray = "=STDEV(IF(tempF=-459.67,"""",tempF))" Range("M18").Select End SubThanks !

ak

I am working on a spreadsheet that I have some arrays with SumIF and

CountIF. I am looking for a way to do a Standard Deviation through an

STDEVIF. I built the following formula, but Excel did not like it one bit.

{=STDEV(IF($F$3:$F$25=$F35,IF(ISNUMBER(H$3:H$25)*(H$3:H$25),0)))}

If I can not do a STDEV IF. Then I thought maybe I could perform a CountIF

in cell H36 and then try to perform an offset type of formula to tell Excel

which cells I would want the STDEV performed on. When I tried this:

STDEV(h36,x) I do not get the correct answer either. Essentially I was

thinking by using multiple countIF's I could perform a STDEV calc.

Let me know your thoughts.

thanks.

Chris

=STDEV(IF(Tracker!AZ7:AZ112>0,"",AZ7:AZ112))/SQRT(COUNTIF(Tracker!AZ7:AZ112,">0"))

I'm a medical student working with test subject files. In order to evaluate wether a certain intervention has an effect or not, I need to evaluate the differences between two groups. I made an excel file with data of every test subject in one tab. At the top of the page, I displayed the group to which the test subject belongs. Now I want to make a summary of all data in a new tab. I want to calculate the standard deviation over previous tabs, with the condition to only calculate the value if the test subject belongs to group 0 or group 1. My formula now is =STDEV(IF('testsubject01'!A1=0;'testsubject01'!B1);IF('testsubject02'!A1=0;'testsubject02'!B1;etc.). In this formula A1 is the groupnumer and B1 is a random data. The problem is the groupnumber. When the groupnumber is 0, the data is used for the calculation of the standard deviation, which is correct. However, when the groupnumer is 1, the data isn't used for the calculation, but this test subject does count for one in the population (he or she adds up in the population (N) of the formula). How could I solve this problem?

I have a dynamic range that I need to be able to calculate the standard deviation for. The dynamic range sometimes has blank cells and #N/A cells which I do not want included in the calculation. However, there are sometimes cells with the number 0 in it (instead of blank) which I do want to include. I have come up with an array formula, but the standard deviation it calculates is not correct. The formula is:

=STDEV(IF(ISNUMBER(A3:A20000)*ISNUMBER(A3:A20000),1,0))

*This is an array formula calculated by pressing CTRL+SHIFT+ ENTER

The number it calculates is 0.43, however, this is incorrect. If you just calculate the standard deviation of the actual data points in excel (cells A138:A5055 in this case) : STDEV(A138:A5055), it returns 0.17. This is the correct standard deviation. Could someone please help me with this?

Kind Regards,

Maani

http://www.4shared.com/file/12409801...c74/Book1.html

values in the calc.. The RSQ has 2 arguments, 1st for y's and 2nd for x's.

My problem is how to do an array formula with 2 arguments.

If I wanted to calc the STDEV ( a 1 argument function ) and exclude zero's I

would enter;

{=STDEV(IF($J11:$AG11<>0,$J11:$AG11))}

For RSQ's normal usage I would enter;

=RSQ(J11:AG11,J7:AG7)

How do I adjust the formula above to exclude zero values?

Bruce

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.

Thanks!!