topic Re: Corrected mean calculation in Discussions

Corrected mean calculation

P_E_Bachara — Sun, 11 Jun 2023 00:00:37 GMT

Hi All,

I struggle to calculate a "corrected mean" out of 4 result columns with continuous numerical values that I get.

How I define the "corrected mean"? I want to exclude the value which is the furthest away from the mean and double count the value closest to it (effectively replacing the excluded "outlier" with the value closes to it).

See example below where excluded values are highlighted in red and duplicated ones in green.

Thanks for your support! (I'm currently on JMP 17.0)

Please find script with the sample raw data below:

New Table( "corrected_mean_script",
	Add Rows( 10 ),
	New Column( "R1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Color Cells( {36, {1, 10}} ),
		Set Values( [50.7, 54.5, 53.7, 54.5, 51.4, 47.6, 50.8, 51.4, 47.1, 53.1] )
	),
	New Column( "R2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Color Cells( {{36, {3, 8}}, {3, {5, 6, 9}}} ),
		Set Values( [49.2, 55.4, 51.3, 54.8, 55.3, 53.7, 51.6, 51.2, 51.8, 50.3] ),
		Set Display Width( 48 )
	),
	New Column( "R3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Color Cells( {{3, {1, 2, 4}}, {36, {5, 7}}} ),
		Set Values( [54.7, 50.3, 55.1, 51.3, 52.9, 48.2, 53, 52.8, 44.1, 51.6] )
	),
	New Column( "R4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Color Cells( {{36, {2, 4, 6, 9}}, {3, {3, 7, 8, 10}}} ),
		Set Values( [50.3, 52, 49.8, 52.9, 50.5, 52.1, 54.3, 44.8, 48.6, 55.3] ),
		Set Display Width( 50 )
	)
)

Re: Corrected mean calculation

Mark_Bailey — Wed, 19 Apr 2023 15:00:29 GMT

Here is one way, in steps, to see a solution.

Names Default To Here( 1 );

dt = New Table( "corrected_mean_script",
	Add Rows( 10 ),
	New Column( "R1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Color Cells( {36, {1, 10}} ),
		Set Values( [50.7, 54.5, 53.7, 54.5, 51.4, 47.6, 50.8, 51.4, 47.1, 53.1] )
	),
	New Column( "R2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Color Cells( {{36, {3, 8}}, {3, {5, 6, 9}}} ),
		Set Values( [49.2, 55.4, 51.3, 54.8, 55.3, 53.7, 51.6, 51.2, 51.8, 50.3] ),
		Set Display Width( 48 )
	),
	New Column( "R3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Color Cells( {{3, {1, 2, 4}}, {36, {5, 7}}} ),
		Set Values( [54.7, 50.3, 55.1, 51.3, 52.9, 48.2, 53, 52.8, 44.1, 51.6] )
	),
	New Column( "R4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Color Cells( {{36, {2, 4, 6, 9}}, {3, {3, 7, 8, 10}}} ),
		Set Values( [50.3, 52, 49.8, 52.9, 50.5, 52.1, 54.3, 44.8, 48.6, 55.3] ),
		Set Display Width( 50 )
	)
);

dt << New Column( "Corrected Mean", Numeric, "Continuous" );

For Each Row(
	mean = Mean( :R1, :R2, :R3, :R4 );
	data = dt[Row(), 1::4];
	absDiff = Abs( data - mean );
	min = (data[Loc( absDiff == Min( absDiff ) )])[1];
	data[Loc( absDiff == Max( absDiff ) )[1]] = min;
	:Corrected Mean = Mean( data );
);

Re: Corrected mean calculation

P_E_Bachara — Wed, 19 Apr 2023 16:04:40 GMT

Many thanks Mark, it works well.

For future reference for others, using tips from this topic should allow to localise the 4 columns in a wider data table:
https://community.jmp.com/t5/Discussions/How-to-get-column-number-from-column-name-JMP/td-p/5987

Thanks again!