Calculating averages within a Data Tabe

[#1]

I am hoping to get some help with calculating averages within a table. I have to be able to calculate the averages of a given input ranges for each of the tests and then create a new column that holds the averages so that I can plot them.  test_1 and test_3 have two (2) band averages and test_3 has three (3) band averages

test_1:

BAND_1 = Average( 8 - 11 )

BAND_2 = Average (14.5 - 17.5 )

test_3:

BAND_1 = Average( 8 - 11 )

BAND_2 = Average (14.5 - 17.5 )

test_2:

BAND_1 = Average( 8 - 10 )

BAND_2 = Average( 8 - 11 )

BAND_3 = Average (14.5 - 17.5 )

I attached a data table as an example of how the data looks currently ( columns 1 - 7 )

columns 8 - 10 was are the averages of the inputs i did manually in Excel

column 11 - 13 were done in Excel as well they are the values of the averages of that specific band

I have a data table with following information

RowCol-Chnl-PartNo = Unique Identifier

Mode = Test ModeQ

TesState = Test StateQA

input = test input

test_1 / test_2 / test_3 = output data.

Thanks in advance

[#2 · Accepted solution]

I have recreated your table using formulas for each of your columns 8-13.  These are based upon a hidden column I created, called "Band".  You can examine each of the formulas to see how I created them.

There is an issue.  In your entry, you treat test 2 differently, in that you specify 3 Bands, but in your sample data table, your Excel created output only shows 2 bands.  Additionally, the Bands are overlapping in definition.  I don't understand how you intend to handle this.  Is your thinking, that rows that have input values of 8-10 will have averages in your Final avg columns, just based on 8-10, and if a row has a value of 10.25-11 that it would have an average of 8-11 rows?

The Base Table2, has a guess as to how the 3 band levels for test3 are.  

See the attached data tables for my response so far

[#3]

Thank you @txnelson. I am looking over this right now.. Yes, unfortunately test_2 has to be treated differently. If you look closely when the mode = Tx only, you will see 3 bands 8-10 (average number @ input 10), 10 - 11 (average number @ input 11) and 14.5 - 17.5 (average number @ input 17.5),

test_2 when mode = Rx it is the same as test_1 and test_3. 2 bands 8-11 (average number @ input 11) and 14.5 - 17.5 (average number @ input 17.5)

I am having the most difficulty with that test_2 @ mode = Tx

New Column( "band",
	numeric,
	Formula(
		If( 
			8 <= :input <= 11, 11,
			14.5 <= :input <= 17.5, 17.5,
			.
		)
	)
);

New Column( "band3",
	numeric,
	Formula(
		If( 
			8 <= :input <= 10 & :mode == "Tx", 10,
			10 < :input <= 11 & :mode == "Tx", 11,
			8 <= :input <= 11 & :mode == "Rx", 11,
			14.5 <= :input <= 17.5, 17.5,
			.
		)
	)
);

[#4]

I modified your formulas and added another band column. This populated the columns correctly..

New Column( "band1",
	numeric,
	Formula(
		If( 
			8 <= :input <= 11, 11,
			14.5 <= :input <= 17.5, 17.5,
			.
		)
	)
);

New Column( "band2",
	numeric,
	Formula(
		If( 
			8 <= :input <= 10 & :mode == "Tx", 10,
			.
		)
	)
);

New Column( "band3",
	numeric,
	Formula(
		If( 
			10 <= :input <= 11 & :mode == "Tx", 11,
			.
		)
	)
);

New Column( "AvgS11M",
	Numeric,
	Formula(
		If( :band1 == :input,
			Col Mean(
				:test_1,
				:Name( "RowCol-Chnl-PartNo" ),
				:mode,
				:teststate,
				:band1
			),
			.
		)
	)
);

New Column( "AvgS22M", 
	Numeric,
	Formula(
		If( :band1 == :input,
			Col Mean(
				:test_3,
				:Name( "RowCol-Chnl-PartNo" ),
				:mode,
				:teststate,
				:band1
			),
			.
		)
	)
);

New Column( "AvgS21M",
	Numeric,
	Formula(
		If( 
			mode == "Rx" & :band1 == :input,  Col Mean( :test_2, :Name( "RowCol-Chnl-PartNo" ), :mode, :teststate, :band1 ),
			mode == "Tx" & :band2 == :input,  Col Mean( :test_2, :Name( "RowCol-Chnl-PartNo" ), :mode, :teststate, :band2 ),	
			mode == "Tx" & :band3 == :input,  Col Mean( :test_2, :Name( "RowCol-Chnl-PartNo" ), :mode, :teststate, :band3 ),		
			.
		)
	)
);


New Column( "S11M_AVG",
	Numeric,
	Formula(
		If( Is Missing( :Band1 ) == 0,
			Col Mean(
				:test_1,
				:Name( "RowCol-Chnl-PartNo" ),
				:mode,
				:teststate,
				:band1
			),
			.
		)
	)
);

New Column( "S22M_AVG",
	Numeric,
	Formula(
		If( Is Missing( :Band1 ) == 0,
			Col Mean(
				:test_3,
				:Name( "RowCol-Chnl-PartNo" ),
				:mode,
				:teststate,
				:band1
			),
			.
		)
	)
);

New Column( "S21M_AVG",
	Numeric,
	Formula(
		If( 
			mode == "Rx" & Is Missing( :Band1 ) == 0, Col Mean( :test_2, :Name( "RowCol-Chnl-PartNo" ), :mode, :teststate, :band1 ),
			mode == "Tx" & Is Missing( :Band2 ) == 0, Col Mean( :test_2, :Name( "RowCol-Chnl-PartNo" ), :mode, :teststate, :band2 ),
			mode == "Tx" & Is Missing( :Band3 ) == 0, Col Mean( :test_2, :Name( "RowCol-Chnl-PartNo" ), :mode, :teststate, :band3 ),
			.
			
		)
	)
);

[#5]

Nice job...….I am glad I could help.....sorry that I didn't see the Tx vs. Rx requirement in your data for the test2.....