Expand my Community achievements bar.

SOLVED

Is there a function in FormCalc for Standard Deviation (Excel StdDev)

Avatar

Level 5
 
1 Accepted Solution

Avatar

Correct answer by
Employee

Thanks, I checked that. SD can be calculated for sample and population. I was using the Population's formula. Now, I have used the sample's and it is working as per your expectations. Fixed Form.

 

For this, the following script is being used:

 

var rows = this.resolveNodes("tblTestData.repeatingRow[*]");
var sm=0; 
var cm=0;
for(var i=0;i<rows.length;i++)
{ if(rows.item(i).airVoids1.rawValue)
{
sm+= Math.pow(rows.item(i).airVoids1.rawValue - form1.page1.tblTestData.row2.amountMeanX.rawValue,2);
cm=cm+1;
}
}
form1.page1.tblTestData.row2.amountDeviation.rawValue = Math.sqrt(sm/(cm-1));

 

View solution in original post

12 Replies

Avatar

Employee

Let's assume you have the mean stored in a variable called 'mean' and the values are in an array called 'values' of length n. Then the way to calculate SD in FormCalc is:

 

sum=0;
for i=0 upto n step 2 do
sum=sum + Math.pow(values[i]-mean,2);
endfor
SD = Math.sqrt(sum/(n));

 in Javascript

 

 

sum=0;
for (i=0; i<n; i++)
{sum+=Math.pow(values[i]-mean,2);}
SD = Math.sqrt(sum/(n);

 

 

 

 

I'm missing something.  Here's a copy of my form.  The Standard Deviation field is under 1. Test Data.

Avatar

Employee

Sorry, but I can't access the form's link. can you please share via Google Drive?

Do you want to calculate the SD of Air Voids column?

Also, in the Mean (X) field, you are calculating the sum of Air Voids. shouldn't it be the average instead of sum?

The calculation for Mean is sum of AirVoids divided by the number of Station.

 

Yes, formula for ST of Air Voids column is what I need 

 

 

 

Avatar

Employee

I have fixed your form for both Mean and SD. I have added the following JavaScript code on the calculate event of SD field:

 

var rows = this.resolveNodes("tblTestData.repeatingRow[*]");
var sm=0; 
var cm=0;
for(var i=0;i<rows.length;i++)
{ if(rows.item(i).airVoids1.rawValue)
{
sm+= Math.pow(rows.item(i).airVoids1.rawValue - form1.page1.tblTestData.row2.amountMeanX.rawValue,2);
cm=cm+1;
}
}
form1.page1.tblTestData.row2.amountDeviation.rawValue = Math.sqrt(sm/cm);

And following code to calculate the Mean:

 

Avg(repeatingRow[*].airVoids1);

This is the fixed form.

The StdDev does not provide the same outcome in the "fixed form" as in the original Excel....see here

Avatar

Correct answer by
Employee

Thanks, I checked that. SD can be calculated for sample and population. I was using the Population's formula. Now, I have used the sample's and it is working as per your expectations. Fixed Form.

 

For this, the following script is being used:

 

var rows = this.resolveNodes("tblTestData.repeatingRow[*]");
var sm=0; 
var cm=0;
for(var i=0;i<rows.length;i++)
{ if(rows.item(i).airVoids1.rawValue)
{
sm+= Math.pow(rows.item(i).airVoids1.rawValue - form1.page1.tblTestData.row2.amountMeanX.rawValue,2);
cm=cm+1;
}
}
form1.page1.tblTestData.row2.amountDeviation.rawValue = Math.sqrt(sm/(cm-1));