# More than 7 nested if statements

#### nat2311

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Gerald Higgins

##### Well-known Member
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

##### MrExcel MVP
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!

Replies
1
Views
116
Replies
6
Views
706
Replies
11
Views
332
Replies
3
Views
371
Replies
2
Views
175

1,195,939
Messages
6,012,425
Members
441,698
Latest member
DaveTeo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back