Help

StDev using only fields that are not empty

Topic Labels: Formulas
359 3
cancel
Showing results for 
Search instead for 
Did you mean: 
BChristle
4 - Data Explorer
4 - Data Explorer

I am new to airtable. I used this  Standard Deviation STDEV - Quick Reference - Airtable Community to calculated the standard deviation. However I have 5 fields in this table and sometimes only 3 or 4 values are entered. I would like to only consider the fields that have a value entered. for n I added a field called number of runs, which helped, but it still considers the fields that are empty and includes them as the value "0". How can we fix this? 

3 Replies 3

Hi,

i simplified naming a bit
Instead of   POWER ( Value1 - Average , 2),
you should change each line to IF( Value1 ,  POWER ( Value1 - Average , 2)),
in the absence of value, difference will be a 0 and won't apply the whole result

- use COUNT( Value1, Value2, .... )   to get n and count average.
- you might also wrap the result formula in IF( NumOfRuns > 1 , ..._the_formula_..  ) to avoid risk of division by 0

You can use this formula to get your formula 😁 , just fix Field names 

'IF(NumOfRuns>1,SQRT(SUM(\n' & REGEX_REPLACE('12345','(\\d)',
'IF(Value$1,POWER(Value$1-Average,2)),\n') & '0)/(NumOfRuns-1)))'

you might need to remove these double quotes, at the beginning and the end 

Alexey_Gusev_0-1737274677245.png

 



Thank you @Alexey_Gusev I am sorry. I still don't understand how that would work. I tried to create a formula the way you described it, but I do not understand some of the things I should replace, like n and d and 12345 and REGEX_REPLACE. Sorry I am not very familiar with CODE language. 

IF (No. of Runs>1, SQRT(  
  SUM(\n'&REGEX_REPLACE('12345','(\\d)',
    IF (Value 1 Field, POWER({Value 1 Field} - {Avg of Values Field}, 2))
    IF (Value 2 Field, POWER({Value 2 Field} - {Avg of Values Field}, 2)),
    IF (Value 3 Field, POWER({Value 3 Field} - {Avg of Values Field}, 2)),
    ...
  ),\n'&'0) / (n - 1)
)

 

Hi,
actually, in the basics, it's usual formula, like in Excel. Except that you are using field name, not A1/B2 - like stuff.  You did almost right, but you just didn't know that field name must be in curly brackets when it contains spaces.

It seems like I did it overcomplicated, sorry for that.



!!! you can omit this part of explanation

REGEX_REPLACE('12345','(\\d)'
, ' some stuff $1 ') is just a way to loop the same 5 times changing $1 as 1,2,3,4,5. 
/n  is to start a new line


So at start it looks like

Alexey_Gusev_0-1737501019189.png


and then you can copy result


Alexey_Gusev_4-1737501819697.png

where you remove "  at start and the end 



!!! and get to the final formula

Alexey_Gusev_5-1737501955457.png


version to copy-paste:

 

IF({No. of Runs}>1,SQRT(SUM(
IF({Value 1 Field},POWER({Value 1 Field}-{Avg of Values Field},2)),
IF({Value 2 Field},POWER({Value 2 Field}-{Avg of Values Field},2)),
IF({Value 3 Field},POWER({Value 3 Field}-{Avg of Values Field},2)),
IF({Value 4 Field},POWER({Value 4 Field}-{Avg of Values Field},2)),
IF({Value 5 Field},POWER({Value 5 Field}-{Avg of Values Field},2)),
0)/({No. of Runs}-1)))