Forum Discussion

anupambit1797's avatar
anupambit1797
Steel Contributor
Mar 11, 2026
Solved

Correlation Study with Filters

Dear Experts , 

                   I have a data like below( Attached worksheet)

And want study the correlation between SNR[0~3], there will be 6 combinations

as below:-

I want make like this but with Filters/Slices for the "File.Name" & "SSB or TRS" & "Carrier Index", different colors for different Carrier Index(0 &1)

 

Thanks & Regards

Anupam Shrivastava

BI & Data Analysis
Charting
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Mar 12, 2026

    I can create tables of correlations but I am not sure about the tables of charts!

    =LET(
    datasetsϑ, BYCOL(SNR, THUNK),
    MAKEARRAY( 3, 3,
        LAMBDA(i, j,
            LET(
                SNR_x, INDEX(datasetsϑ, 1, i + 1)(),
                SNR_y, INDEX(datasetsϑ, 1, j)(),
                r, IF(i >= j, CORREL(SNR_x, SNR_y), ""),
                r
            )
        )
    )
)

     

2 Replies

  • Olufemi7's avatar
    Olufemi7
    Iron Contributor
    Mar 16, 2026

    Hello anupambit1797​,

    Yes this can be done in Excel although Excel does not have a built in scatter matrix chart like the one in your example.

    With your dataset containing File.Name, SSB or TRS, Carrier Index and SNR [0] to SNR [3], you can create six scatter charts for the SNR combinations SNR[0] vs SNR[1], SNR[0] vs SNR[2], SNR[0] vs SNR[3], SNR[1] vs SNR[2], SNR[1] vs SNR[3] and SNR[2] vs SNR[3]. Arrange the charts in a grid to resemble a correlation matrix.

    To show different colors for Carrier Index add two series in each chart filtered by Carrier Index so that Carrier Index 0 and Carrier Index 1 appear with different colors.

    If the data is stored in an Excel Table you can also insert slicers for File.Name, SSB or TRS and Carrier Index. The slicers will filter the dataset and update all charts.

    In Excel 365 another option is Python in Excel which can generate the scatter matrix automatically using the columns SNR [0] to SNR [3] and color the points by Carrier Index while filtering by File.Name or SSB or TRS.

    Correlation values for each pair can also be calculated using the CORREL function.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 12, 2026

    I can create tables of correlations but I am not sure about the tables of charts!

    =LET(
    datasetsϑ, BYCOL(SNR, THUNK),
    MAKEARRAY( 3, 3,
        LAMBDA(i, j,
            LET(
                SNR_x, INDEX(datasetsϑ, 1, i + 1)(),
                SNR_y, INDEX(datasetsϑ, 1, j)(),
                r, IF(i >= j, CORREL(SNR_x, SNR_y), ""),
                r
            )
        )
    )
)