Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Calculating averages within a Data Tabe

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 29, 2020 6:53 AM
(349 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jim

4 REPLIES 4

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating averages within a Data Tabe

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,
.
)
)
);
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating averages within a Data Tabe

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 ),
.
)
)
);
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating averages within a Data Tabe

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.....

Jim