Forum Discussion
Solved
Index & Match Function
I am trying to calculate a percentage return using the following formula:
=INDEX($L$51:$L$222,MATCH($N1,$B$51:$B$222,0))/INDEX($L$51:$L$222,MATCH(EOMONTH($N1,-60), $B$51:$B$222,0))-1
I am getting an N/A# message. The formula seems to work for last 12 months but at 36 and 60 months I get an error message.
Any help would be appreciated. Thank you.
Regards,
Perhaps there are no data for 36 and 60 months ago?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
Perhaps there are no data for 36 and 60 months ago?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
Thank you, Hans. Problem solved.
However, I have another question. I am calculating standard deviation for a data set. I have tried the following two formulas on the same data set. However, I am getting different results. Any thoughts as to why? Thank you.
=STDEV.P(FILTER(D$48:D$437,$A$48:$A$437>EOMONTH($B7,-60)))*SQRT(12)
=STDEV.P(D146:D206)*SQRT(12)
Hi Richard,
That is impossible to answer without seeing a copy of the workbook.
