Forum Discussion

Arnaud1010's avatar
Arnaud1010
Copper Contributor
May 21, 2022
Solved

OFFSET nested in COVARIANCE.S returns a #NUM! error

Hi!   I am working on a project for work that involves building a covariance matrix. The data are in another sheet ('RTN (%)') hence my formula: =COVARIANCE.S(OFFSET('RTN (%)'!$B$119,0,COLUMN(B2)-2...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    May 22, 2022

    Arnaud1010 

     

    Assuming I understood...

    in MATRIX!B2:

    =COVARIANCE.S(
     OFFSET(DATA!$B$2, 0, INDEX(COLUMN(B$1)-2,1), COUNTA(DATA!$A:$A)),
     OFFSET(DATA!$B$2, 0, INDEX(ROW($A2)-2,1), COUNTA(DATA!$A:$A))
)

     

    If this does what you want and run Excel 2021 or 365 see (in attached file) another option using XLOOKUP instead of OFFSET

Arnaud1010's avatar
Arnaud1010
Copper Contributor
May 21, 2022

Lorenzo

 

Thanks so much for your help. You are right the formula I posted can be simplified however I need this to be dynamic as I want a matrix as a final result.

 

Any idea how to solve it? 🤔

Lorenzo's avatar
Lorenzo
Silver Contributor
May 21, 2022

Arnaud1010 

however I need this to be dynamic as I want a matrix as a final result

I'm afraid I don't understand. Could you try again and maybe post picture(s) to illustrate what you have & expect?

  • Arnaud1010's avatar
    Arnaud1010
    Copper Contributor
    May 22, 2022

    Lorenzo

     

    I reproduced the spreadsheet but can't post it somehow. Please find below the screenshots. Raw data is in DATA tab and the covariance Matrix is in MATRIX tab.

     

    MATRIX tab

    The final result is a Matrix of n*n size that needs to refer to the proper columns in DATA tab.

     

    DATA tab

    This is the data tab.

    And the formula (in B2) looks like that, but as you can see produce the #NUM! error...

    Formula in the matrix cell

    Thanks for your help!

    Arnaud

     

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 22, 2022

      Arnaud1010 

       

      Assuming I understood...

      in MATRIX!B2:

      =COVARIANCE.S(
     OFFSET(DATA!$B$2, 0, INDEX(COLUMN(B$1)-2,1), COUNTA(DATA!$A:$A)),
     OFFSET(DATA!$B$2, 0, INDEX(ROW($A2)-2,1), COUNTA(DATA!$A:$A))
)

       

      If this does what you want and run Excel 2021 or 365 see (in attached file) another option using XLOOKUP instead of OFFSET

      • Arnaud1010's avatar
        Arnaud1010
        Copper Contributor
        May 23, 2022
        Lorenzo

        Amazing, thank you so much! It works like a charm.

        Take care,
        Arnaud

Resources