Free Microsoft Excel 2013 Quick Reference

stdev if?

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


Hey everyone I have been trying to find a way to create a formula to act like a stdevif function. I have found many examples on how to do it on this forum when there is only one data set, but I haven't seen any with two. Or maybe I am simply unable to understand how to apply this formula

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

Greetings all!

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.

I am having trouble figuring out a way to calulate the stdev of certain values.
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?

Hi y'all! Have a worksheet that has total averages for each row that has data in it. Some rows may contain a few empty cells. When I place the following formula: =STDEV(IF(B3:IO3,">0",B3:IO3)) , on the adjacent column to the total average, I get a 0 for every result, but if I place: =STDEV(IF(B3:IO3,B3:IO3)), my results come up but on some I get a #DIV/0 error. How do I fix this and without a calculation of those empty cells as 0? The formula I used for the total average is: =SUMIF(J3:IO3,">0")/MAX(1,COUNTIF(J3:IO3,">0")), not that it matters. Thanks in advance!

Thanks for everyone's help Friday with my AVERAGE IF, and blank cells problem: how to make AVERAGE IF ignore blank cells when working with two columns. To further this line of thought, my next step is modifying my standard error formula for the same reason. my current standard error formula in plain english is:

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!

My formula is {=STDEV(IF(Data!1:33="1",Data!E:IV))} and the results is
'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?

Good Morning:

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

I am trying to calculate the standard deviation of two populations of data from a mix of three and I can't seem to get it to work not sure what I am doing wrong.

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

Good Morning:

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

My formula is {=STDEV(IF(Data!1:33="1",Data!E:IV))} and the results is
'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?

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

Looking to calculate the standard deviation of all the values in column C where the corresponding values in column O equal to "yes".

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.

Hi,

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

I want to do an array formula for the RSQ function so that it excludes zero
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

I have an array formula that I am trying to integrate into a macro I'm writing. The array uses an if statement to sort my data by frame number and then calculates the standard deviation for each frame. As of right now the code seems to work and calculates for the first frame but then just displays this first value in every cell.
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 With
I've attached a sample workbook. I think this should be a simple fix I just can't seem to figure it out.

Hi guys...

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.

Ok Ive search and and so far coming up stumped on this one. I have the following formula that I am inserting into a cell using a macro.

=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

Hi - I have written a simple macro that takes a column of data & creates 3 temperature columns of data tempK, tempC, tempF. These are actually Names that I create.
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 Sub
Thanks !

ak

Hey guys,

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

I'm trying to calculate standard error without taking into account cells that contain a value of 0. I've written the following array formula but it has returned a value of 0 for all data sets that I am trying to calculate. Can someone point out my error or give me a different formula to try? Thanks a lot!

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

Dear everyone,

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?

Hello,

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

I want to do an array formula for the RSQ function so that it excludes zero
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

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.

Thanks!!