Date: Wed, 19 Jan 2022 00:48:26 +0000 (GMT) Message-ID: <1844537661.105546.1642553306488@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_105545_1483106149.1642553306488" ------=_Part_105545_1483106149.1642553306488 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - Conditional Calculations Functions

EXAMPLE - Conditional Calculations Functions

This example illustrates how you can use the following condition= al calculation functions to analyze weather data:

• AVERAGEIF - Average of a set of values by group that meet = a specified condition. See AVE= RAGEIF Function.
• MINIF - Minimum of a set of values by group that meet a sp= ecified condition. See MINIF Funct= ion.
• MAXIF - Maximum of a set of values by group that meet a sp= ecified condition. See MAXIF Funct= ion.
• VARIF - Variance of a set of values by group that meet a s= pecified condition. See VARIF Func= tion.
• STDEVIF - Standard deviation of a set of values by group t= hat meet a specified condition. See STDEVIF Function.

Source:

Here is some example weather data:

=20 =20 =20 =20 =20 =20
date city rain temp wind
1/23/17 Valleyville 0.00 12.8 6.7
1/23/17 Center Town 0.31 9.4 5.3
1/23/17 Magic Mountain 0.00 0.0 7.3
1/24/17 Valleyville 0.25 17.2 3.3
1/24/17 Center Town 0.54 1.1 7.6
1/24/17 Magic Mountain 0.32 5.0 8.8
1/25/17 Valleyville 0.02 3.3 6.8
1/25/17 Center Town 0.83 3.3 5.1
1/25/17 Magic Mountain 0.59 -1.7 6.4
1/26/17 Valleyville 1.08 15.0 4.2
1/26/17 Center Town 0.96 6.1 7.6
1/26/17 Magic Mountain 0.77 -3.9 3.0
1/27/17 Valleyville 1.00 7.2 2.8
1/27/17 Center Town 1.32 20.0 0.2
1/27/17 Magic Mountain 0.77 5.6 5.2
1/28/17 Valleyville 0.12 -6.1 5.1
1/28/17 Center Town 0.14 5.0 4.9
1/28/17 Magic Mountain 1.50 1.1 0.4
1/29/17 Valleyville 0.36 13.3 7.3
1/29/17 Center Town 0.75 6.1 9.0
1/29/17 Magic Mountain 0.60 3.3 6.0

Transformation:

The following computes average temperature for rainy days by city:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula AVERAGEIF(temp, rain > 0) city 'avgTempWRain'
=20

The following computes maximum wind for sub-zero days by city:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula MAXIF(wind,temp < 0) city 'maxWindSubZero'
=20

This step calculates the minimum temp when the wind is less than 5 mph b= y city:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula MINIF(temp,wind<5) city 'minTempWind5'
=20

This step computes the variance in temperature for rainy days by city:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula VARIF(temp,rain >0) city 'varTempWRain'
=20

The following computes the standard deviation in rainfall for Center Tow= n:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula STDEVIF(rain,city=3D=3D'Center Town') city 'stDevRainCT'
=20

You can use the following transforms to format the generated output. Not= e the \$col placeholder value for the multi-column transforms:<= /p>

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Edit column with formula stDevRainCenterTown,maxWindSubZero<= /td>=20 numformat(\$col,'##.##')
=20

Since the following rely on data that has only one significant digit, yo= u should format them differently:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Edit column with formula varTempWRain,avgTempWRain,minTempWind5 numformat(\$col,'##.#')
=20

Results:

=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
date city rain temp wind avgTempWRain maxWindSubZero minTempWind5 varTempWRain stDevRainCT
1/23/17 Valleyville 0.00 12.8 6.7 8.3 5.1 7.2 63.8 0.37
1/23/17 Center Town 0.31 9.4 5.3 7.3   5 32.6 0.37
1/23/17 Magic Mountain 0.00 0.0 7.3 1.6 6.43 -3.9 12 0.37
1/24/17 Valleyville 0.25 17.2 3.3 8.3 5.1 7.2 63.8 0.37
1/24/17 Center Town 0.54 1.1 7.6 7.3   5 32.6 0.37
1/24/17 Magic Mountain 0.32 5.0 8.8 1.6 6.43 -3.9 12 0.37
1/25/17 Valleyville 0.02 3.3 6.8 8.3 5.1 7.2 63.8 0.37
1/25/17 Center Town 0.83 3.3 5.1 7.3   5 32.6 0.37
1/25/17 Magic Mountain 0.59 -1.7 6.4 1.6 6.43 -3.9 12 0.37
1/26/17 Valleyville 1.08 15.0 4.2 8.3 5.1 7.2 63.8 0.37
1/26/17 Center Town 0.96 6.1 7.6 7.3   5 32.6 0.37
1/26/17 Magic Mountain 0.77 -3.9 3.0 1.6 6.43 -3.9 12 0.37
1/27/17 Valleyville 1.00 7.2 2.8 8.3 5.1 7.2 63.8 0.37
1/27/17 Center Town 1.32 20.0 0.2 7.3   5 32.6 0.37
1/27/17 Magic Mountain 0.77 5.6 5.2 1.6 6.43 -3.9 12 0.37
1/28/17 Valleyville 0.12 -6.1 5.1 8.3 5.1 7.2 63.8 0.37
1/28/17 Center Town 0.14 5.0 4.9 7.3   5 32.6 0.37
1/28/17 Magic Mountain 1.50 1.1 0.4 1.6 6.43 -3.9 12 0.37
1/29/17 Valleyville 0.36 13.3 7.3 8.3 5.1 7.2 63.8 0.37
1/29/17 Center Town 0.75 6.1 9.0 7.3   5 32.6 0.37
1/29/17 Magic Mountain 0.60 3.3 6.0 1.6 6.43 -3.9 12 0.37
------=_Part_105545_1483106149.1642553306488--