More than 7 nested if statements

[nat2311]

Hi,

I am trying to apply this formula to the data 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

[shg]

I suggest you create two dynamic named ranges that point to the data for the year of interest, and then use a much simpler formula:

= COVAR(range1, range2) / VARP(range2)

I can't tell you how to do that without seeing your data laid out in a workbook.

[oldchippy]

Hi,

May be a lookup table could be the answer?

http://www.contextures.com/xlFunctions02.html

[nat2311]

Thanks for the responses. How would I go about creating two dynamically named ranges? The data is laid out E corresponding to 1998 (the first year with data) to O corresponding to 2008 (the last year with data). It outputs in C the beta (what I am trying to calculate).

I am also using a different worksheet (Index RET) with C corresponding to 1998 and ending with M corresponding to 2008.

Sorry if this seems basic, but how would I go about constructing these ranges?

Thanks

[shg]

I can't tell you how to do that without seeing your data laid out in a workbook.