More than 7 nested if statements

[nat2311]

Hi,

I am trying to apply this formula to the [COLOR=blue ! important][COLOR=blue ! important]data[/COLOR][/COLOR] for the first year that data exists
whether it be 1998 all the way until 2007. E is the first year I have data and O is the last year.

I'm trying to use a formula that nests more than 7 functions:

=IF(ABS(E10)>0,COVAR(E10:O10, 'INDEX RET'!$C$222:$M$222)/VARP('INDEX RET'!$C$222:$M$222), IF(ABS(F10)>0,COVAR(F10:O10, 'INDEX RET'!$D$222:$M$222)/VARP('INDEX RET'!$D$222:$M$222),
IF(ABS(G10)>0,COVAR(G10:O10, 'INDEX RET'!$E$222:$M$222)/VARP('INDEX RET'!$E$222:$M$222),
IF(ABS(H10)>0,COVAR(H10:O10, 'INDEX RET'!$F$222:$M$222)/VARP('INDEX RET'!$F$222:$M$222),
IF(ABS(I10)>0,COVAR(I10:O10, 'INDEX RET'!$G$222:$M$222)/VARP('INDEX RET'!$G$222:$M$222),
IF(ABS(J10)>0,COVAR(J10:O10, 'INDEX RET'!$H$222:$M$222)/VARP('INDEX RET'!$H$222:$M$222),
IF(ABS(K10)>0,COVAR(K10:O10, 'INDEX RET'!$I$222:$M$222)/VARP('INDEX RET'!$I$222:$M$222),
IF(ABS(L10)>0,COVAR(L10:O10, 'INDEX RET'!$J$222:$M$222)/VARP('INDEX RET'!$J$222:$M$222),
IF(ABS(M10)>0,COVAR(M10:O10, 'INDEX RET'!$K$222:$M$222)/VARP('INDEX RET'!$K$222:$M$222),
IF(ABS(N10)>0,COVAR(N10:O10, 'INDEX RET'!$L$222:$M$222)/VARP('INDEX RET'!$L$222:$M$222), COVAR(O10:O10, 'INDEX RET'!$M$222:$M$222)/VARP('INDEX RET'!$M$222:$M$222))))))))))))

Is there any way around that, or is there any way to simplify this formula?

Thanks

 

[Gerald Higgins]

You could break this into two separate formulas, one looking at Cols E to K, and the other at Cols L to O.
Then use a separate formula to determine which result to use.

Alternatively, consider a lookup.
For example, if you can do the COVAR/VARP formula at the top of the relevant columns, perhaps you can set up a lookup to pick up the relevant result.

 

[pgc01]

Hi nat2311
Welcome to the board

Try:

=COVAR(INDEX(E10:O10,MATCH(TRUE,ABS(E10:O10)>0,0)):O10,INDEX('INDEX RET'!$C$222:$M$222,MATCH(TRUE,ABS(E10:O10)>0,0)):'INDEX RET'!$M$222)/VARP(INDEX('INDEX RET'!$C$222:$M$222,MATCH(TRUE,ABS(E10:O10)>0,0)):'INDEX RET'!$M$222)

... confirm with CTRL-SHIFT-ENTER, not just ENTER!