1. P

    Array formula (stdev if function) -> how to ignore blanks?

    Hello, Can anyone help me find a way for this function to ignore blank cells? Yellow cell = Result without blank row {=STDEV.S(IF(A1:A3=A1,C1:C3),IF(B1:B3=B1,(D1:D3)*-1))} Green cell = Result with blank row {=STDEV.S(IF(A1:A4=A1,C1:C4),IF(B1:B4=B1,(D1:D4)*-1))} The goal: green cell result...
  2. N

    STDEV, IF, AND instead of STDEVIFS

    Based on the posts I've reviewed I'm not the only one looking for a STDEVIFS function in Excel. I'm trying to get along without it by using IF and AND. There are two criteria that need to be matched and if they match I want the STDEV. I am trying to get Excel to calculate STDEV for a...
  3. R

    Standard deviation on specific days

    I have some data that I need averages and standard deviations on specific days. Averages are easy with Averageif but I can't find anything similar for STDEV. I've tried putting a IF statement inside STDEV but keep getting NAME or VALUE errors. My data looks like this: Data...
  4. P

    Need some help with this STDEV formula

    Hello. I've got this formula that I use to give me the STDEV of all data in column "E" from the previous month. It works just as it should, but I was wondering if there is any way to change it to exclude blank cells. Right now, if the cell is blank it calculates it as 0, and it throws off my...
  5. R

    How to determine the number of cells in a range that are not empty?

    Hello, I do a lot of calculations involving ranges, but I realized I get erroneous results depending on whether all or some of the cells in the range contain numbers. Here is a hypothetical example: <tbody> STDEV STDEV/n 83 78 96 92 91 85 90 6.148944935<strike></strike>...
  6. J

    STDEV to ignore empty cells, but it's within an existing array.

    Greetings Excel gurus, A common question with stdev functions is "how do I get stdev to ignore empty cells or zero entries". I've seen answers to this, however, my stdev is already within an array, and I'm having trouble getting the function below to ignore empty cells or zero values. Any...
  7. smide

    Calculate standard deviation of same text values between cells in two columns

    Hello. In columns B and C (B2:C600) I have a product list (about 25 different products) and in columns E and F their prices. When the product is in Column B his price is in Column E (same row) and when the product is in Column C his price is in Column F. In column H (from cell H2 and...
  8. D

    STDEV With 3 Criteria

    Hello, I have an STDEV array that calculates with 2 criteria, but It won't calculate with three. How can I get this to work? Works =STDEV(IF(Amazon!$M$2:$M$54330>=Summary!D6-52,IF(Amazon!$M$2:$M$54330<=Summary!D6-7,Amazon!$I$2:$I$54330))) =STDEV(IF([All Dates]>=[Start Date],IF([All...
  9. R

    Speeding up a STDEV calculation

    =IFERROR(STDEV(IF(('All Data'!$F:$F=[@Stuffername])*('All Data'!$AE:$AE<6),'All Data'!$AB:$AB)),"") The above is the equation I'm using to get the standard deviation of values on a separate sheet with several conditions. The other sheet has about 21000 rows of data. I'm doing the STDEV calc...
  10. C

    formula to calculate STDEV from totals

    Hi, In the picture below I have a formula that calculates the STDEV of the totals of the rows in the table. Is there a formula that would calculate the STDEV for the totals, giving the same result, without the separate SUM formulas that are currently present in Col E? Many thanks
  11. C

    Alternative to Array formula in Excel Cells CTRL+Shift+Enter

    Good Morning,Afternoon,Evening, I am comfortable with using array forumal in excel, however the peice of software i'm using that uses excel templates does not. Does anyone have an anser to an alternative formula for the following: =STDEV(if(group = grouprange, range)) CTRL+SHIFT+ENTER I...
  12. S

    STDEV with multiple ifs

    Hello guys, Can someone help me with this, please? The average function works well but... can STDEV accomplish all the ifs in one single formule like the mean does? I mean the scenarios I, II, III. <colgroup><col span="9"></colgroup><tbody> I II III Rater 1 4 4 0 Rater 2...
  13. M

    VBA - struggling to calc and write StDev data into an array with a For Next loop

    I have made several attempts, this is the latest iteration. It shouldn't produce meaningful output until the minimum periods have been looped thru (volstperiod = 10). --PctChg1() is an array which holds percent change data from i=2 to i = 2541... declared as variant --volstperiod = 10...
  14. C

    Referencing a cell using ADDRESS

    I have three columns of information and I want to talk the STDEV of a certain range of data in the third column based on information in the first. So based on whatever I put into columns A-C, E1 and F1 will change to give the first and last values in column 1 that I am interested in - these are...
  15. I

    Standard Deviation of a matched cell in all worsheets

    I have written a formula that will take what is written in Column A of my Cover sheet and match it to Column A of all the other worksheets (other worksheets defined as "CMM_Dates") which will inturn take the Column E values of all the CMM_Dates worksheets with a match in Column A and average...
  16. A

    Array Question

    Hi all, I'm just beginning to verse myself with arrays and writing my own functions and was hoping someone could walk me through the following function. Not so much the formula for stdDev, but sytax such as mean(k,Arr)....what is that doing. I'm still a bit uncomfortable with arrays...
  17. G

    What's Going On With Curly Braces

    In an Excel 2010 spreadsheet I inherited, I am having trouble with a STDEV function returning a division by zero error. In the formula below, I need to change the BW column to CK. When I look at the formula in a working cell in the formula window, there are curly braces at each end. As soon...
  18. R

    WorksheetFunction StDev error (easy?)

    'Unable to get the StDev property of the WorkSheetFunction class' Ok guys, this error isn't too difficult (I don't think)... I have problems with my newSTDEV function when a worksheet only has one line of entered data in it. I believe it's because you can't take StDev with only one value so...
  19. N

    Use index function to shift range in a patterned formula

    Hi all, I have been playing around with a function all day and it is driving me nuts. My goal is to automatically calculate the volatility of a warrant's (equity) price over time as it approaches the end of its term (in this case 60 months). I want to create a formula that takes the STDEV of...
  20. B

    Calculations based on VLOOKUP?

    I have about 30,000 rows of data, each row has performance data for a single day for an athlete. The days--and rows--vary by athlete. I would like to do some calculations on another sheet based on the VLOOKUP function for each player. For example, if I want to calculate the standard deviation...

Some videos you may like

This Week's Hot Topics