•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
StDev formulas all giving #DIV/0! despite no "0" or error cells
Our take
Are you frustrated by the persistent #DIV/0! error in your STDEV.P formula, despite having no visible zeros in your data? Many users encounter this issue when trying to calculate standard deviation from dynamically generated values. In your case, the formula in column H is likely returning empty strings instead of numeric values, which can lead to the error. Let's explore how to troubleshoot this problem effectively, ensuring you can confidently derive the insights you need from your data.
In column H I have an average speed calculated using columns E and F, whilst excluding outlier values. I am using the formula below:
=IF(AND(160>F3/(E3/60),F3/(E3/60)>0),F3/(E3/60),"")
On another sheet in cell "C5" I am trying to find the SD of column H using the formula:
=STDEV.P('EV Data'!H3:H100000)
The #Div/0! error keeps coming up despite there being no zeros due to the formula in column H. I have also checked using the filter function and there are no ERROR, DIV/0!, or N/A cells in column H. Any help would be greatly appreciated. TIA
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Standard Deviation - Manual vs. Excel FormulaHi all, Having some issues with understanding how Excel is calculating the data for SD. Tried it using both manually and using the =STDEV.S function. https://preview.redd.it/a5y2rc12c1vg1.png?width=1388&format=png&auto=webp&s=2dda4cc8f87617ed6b33108ee30f690b7f561004 Left hand side: S.D is 0.12 (=STDEV.S) Right hand side: using this method: Step 1: Find the mean. Step 2: Subtract the mean from each score. Step 3: Square each deviation. Step 4: Add the squared deviations. Step 5: Divide the sum by one less than the number of data points. Step 6: Take the square root of the result from Step 5. Now, as you can see in yellow, the values don't match. I verified that the =STDEV.S is using the "n-1" method, which matches my manual method. Any idea what I'm missing here? submitted by /u/Byrne-88 [link] [comments]
- why am I getting #Div/0!https://preview.redd.it/xqnntnja4xlg1.png?width=531&format=png&auto=webp&s=9d9f2709a174e9da90850ff5156e45850a937d06 https://preview.redd.it/0fojcr3g4xlg1.png?width=569&format=png&auto=webp&s=7cdb825c6f1724fa960f4854b89394b2d416995d I'm trying to subtract a number to this highlighted green formula. I've been trying to subtract it from a cell in another tab, which wasn't working, and now even if I just try to subtract a hard coded number I get the same result. #DIV/0 shows up in this cell and all the other cells connected with these tab no matter what I'm trying. What's going on? submitted by /u/justonemorepeakmom [link] [comments]