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