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 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
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 23, 2022
      Glad I could help Arnaud1010 and Thanks for providing feedback

Resources