Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

StDev using only fields that are not empty

Topic Labels: Formulas
416 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)))