Greetings,
I have a worksheet with monthly returns for numerous mutual funds, however not all of the funds have the same start date. Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Fund A
[/TD]
[TD="align: center"]Fund B
[/TD]
[TD="align: center"]Fund C
[/TD]
[/TR]
[TR]
[TD="align: center"]1/31/2000
[/TD]
[TD="align: center"]2.96
[/TD]
[TD="align: center"]NA
[/TD]
[TD="align: center"]NA
[/TD]
[/TR]
[TR]
[TD="align: center"]2/28/2000
[/TD]
[TD="align: center"]2.64
[/TD]
[TD="align: center"]-2.3
[/TD]
[TD="align: center"]NA
[/TD]
[/TR]
[TR]
[TD="align: center"]3/31/2000
[/TD]
[TD="align: center"].36
[/TD]
[TD="align: center"]-1.4
[/TD]
[TD="align: center"]NA
[/TD]
[/TR]
[TR]
[TD="align: center"]4/30/2000
[/TD]
[TD="align: center"]-3.24
[/TD]
[TD="align: center"]-1.3
[/TD]
[TD="align: center"]1.6
[/TD]
[/TR]
</tbody>[/TABLE]
In a separate worksheet I need to pull in the dates and returns for a given fund, but exclude periods with NA's. I've attempted to use the INDEX function, which allows me the desired flexibility of toggling between funds:
For Funds =INDEX('Fund Returns'!$B$2:$AI$1113,A6,4)
For Dates =INDEX('Fund Returns'!$B$2:$AI$1113,A6,1)
the issue is that the above formulas are pulling in all months, even those with NA's. How can I modify my formulas to exclude months with NA's?
Thanks!!
I have a worksheet with monthly returns for numerous mutual funds, however not all of the funds have the same start date. Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Fund A
[/TD]
[TD="align: center"]Fund B
[/TD]
[TD="align: center"]Fund C
[/TD]
[/TR]
[TR]
[TD="align: center"]1/31/2000
[/TD]
[TD="align: center"]2.96
[/TD]
[TD="align: center"]NA
[/TD]
[TD="align: center"]NA
[/TD]
[/TR]
[TR]
[TD="align: center"]2/28/2000
[/TD]
[TD="align: center"]2.64
[/TD]
[TD="align: center"]-2.3
[/TD]
[TD="align: center"]NA
[/TD]
[/TR]
[TR]
[TD="align: center"]3/31/2000
[/TD]
[TD="align: center"].36
[/TD]
[TD="align: center"]-1.4
[/TD]
[TD="align: center"]NA
[/TD]
[/TR]
[TR]
[TD="align: center"]4/30/2000
[/TD]
[TD="align: center"]-3.24
[/TD]
[TD="align: center"]-1.3
[/TD]
[TD="align: center"]1.6
[/TD]
[/TR]
</tbody>[/TABLE]
In a separate worksheet I need to pull in the dates and returns for a given fund, but exclude periods with NA's. I've attempted to use the INDEX function, which allows me the desired flexibility of toggling between funds:
For Funds =INDEX('Fund Returns'!$B$2:$AI$1113,A6,4)
For Dates =INDEX('Fund Returns'!$B$2:$AI$1113,A6,1)
the issue is that the above formulas are pulling in all months, even those with NA's. How can I modify my formulas to exclude months with NA's?
Thanks!!