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,5,1),OFFSET('RTN (%)'!$B$119,0,ROW(B2)-2,5,1))

 

This formula returns a #NUM! error

 

However, if I test this formula separately (one cell with the first offset, another with the second offset and then a third cell with the Covariance), it works perfectly.

 

Could you please assist me in solving/finding where it comes from?

 

Thanks so much for your help.

Arnaud

 

Note 1: Exact same experience with INDIRECT instead of OFFSET function. Separately it works, nested it does return #NUM!

Note 2 : If I replace COVARIANCE.S by SUM or STDEV.S it works perfectly : =STDEV.S(OFFSET('RTN (%)'!$B$119,0,COLUMN(B2)-2,3,1),OFFSET('RTN (%)'!$B$119,0,ROW(B2)-2,3,1))

Note 3: I reduced the range on purpose that there are only data without any error. Data targeted is below: 

 

0.024212021
0.010301871
0.014794411
-0.011439908
-0.014387986

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

7 Replies

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

    Hi Arnaud1010 

     

    Issue reproed. However, regarding your 2 OFFSETs:

    - COLUMN(B2)-2 = 0

    - ROW(B2)-2 = 0

    So, your OFFSETs could be simplified as:

    - OFFSET('RTN (%)'!$B$119,0,0,5,1) or even OFFSET('RTN (%)'!$B$119,,,5)

     

    =COVARIANCE.S(OFFSET('RTN (%)'!$B$119,,,5), OFFSET('RTN (%)'!$B$119,,,5))

    works here, I mean it doesn't raise any error

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

Resources