Forum Discussion

ABlea010's avatar
ABlea010
Copper Contributor
Jan 17, 2024
Solved

Update formulas automatically when in/out sample size changes

I have been trying to figure out if this is possible and will try to explain the best I can. I have a worksheet where my In and out sample sizes change daily. I have a separate worksheet to calculate the In and Out sample sizes. So today, the in-sample size is 5:616. The out-sample size is 617:692. Tomorrow, the in-sample size will be 5:619. And the out sample will be 620:695.

My problem that I am trying to solve is can these be updated automatically corresponding to the date and corresponding sample sizes across different types of formulas. I will give a few examples so hopefully I'm making sense.

 

 

Above is a picture of sample size data as mentioned earlier.
Here are a couple formulas from today:
=RSQ(M5:M616,G5:G616)
=RSQ(M617:M692,G617:G692)
=SQRT(SUMSQ(Q5:Q616)/COUNTA(Q5:Q616))
=SQRT(SUMSQ(Q617:Q692)/COUNTA(Q617:Q692))

Now tomorrow I will manually go in and update the sample sizes in multiple formulas. Is there a way for this to be done automatically?

 

 

 

Formulas and Functions
  • mtarler's avatar
    mtarler
    Jan 17, 2024

    yes you can. the return of a INDEX() formula is actually a cell reference and can be use in the range so try something like:
    =RSQ(
    INDEX(M:M, XLOOKUP(A1, table[Current Date], table[In Sample Start])):
    INDEX(M:M, XLOOKUP(A1, table[Current Date], table[In Sample End]),
    INDEX(G:G, XLOOKUP(A1, table[Current Date], table[In Sample Start])):
    INDEX(G:G, XLOOKUP(A1, table[Current Date], table[In Sample End]))

    that is based on A1 having the date you want to use and the above table being called 'table'

2 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 17, 2024

    yes you can. the return of a INDEX() formula is actually a cell reference and can be use in the range so try something like:
    =RSQ(
    INDEX(M:M, XLOOKUP(A1, table[Current Date], table[In Sample Start])):
    INDEX(M:M, XLOOKUP(A1, table[Current Date], table[In Sample End]),
    INDEX(G:G, XLOOKUP(A1, table[Current Date], table[In Sample Start])):
    INDEX(G:G, XLOOKUP(A1, table[Current Date], table[In Sample End]))

    that is based on A1 having the date you want to use and the above table being called 'table'

Resources