Hey guys, I am having loads of trouble editing my spreadsheet and it seems to be taking a really long time (over 20,000 entries)... I have some data for over 500 companies (1990-2015) and they all have different currency units which i need to convert (into EUR). It looks something like this Date Comp Curr Div Rep 1990 x CAD 40 7 1991 x CAD 25 0 * * * * * 2015 x CAD 13 6 1990 y USD 11 2 1991 y USD 43 0 * * * * * 2015 y USD 13 8 1990 z ATS 23 3 1991 z ATS 45 4 * * * * * 2015 z ATS 34 5 1990 a BEF 23 45 * * * * * I then have created a matrix (26,45) of all the historical exchange rates: AED ATS AUD BEF BRL CAD CHF CLP*ZWD 1990 1991 1992 1993 1994 1995 * 2015 What I need is a formula that can recognise the units the companies 'dividends' and 'repurchases' is in and then divide by the correct exchange rate (that is by the correct entry in the matrix)?

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

IF function using matrix entries? HELP

Thread starter ph282
Start date Jun 22, 2016
Tags

formula & macro formula =if() matrix

ph282

New Member

Jun 22, 2016

Hey guys, I am having loads of trouble editing my spreadsheet and it seems to be taking a really long time (over 20,000 entries)...

I have some data for over 500 companies (1990-2015) and they all have different currency units which i need to convert (into EUR). It looks something like this

Date	Comp	Curr	Div	Rep
1990	x	CAD	40	7
1991	x	CAD	25	0
*	*	*	*	*
2015	x	CAD	13	6
1990	y	USD	11	2
1991	y	USD	43	0
*	*	*	*	*
2015	y	USD	13	8
1990	z	ATS	23	3
1991	z	ATS	45	4
*	*	*	*	*
2015	z	ATS	34	5
1990	a	BEF	23	45
*	*	*	*	*

I then have created a matrix (26,45) of all the historical exchange rates:

AED ATS AUD BEF BRL CAD CHF CLP*ZWD
1990
1991
1992
1993
1994
1995
*
2015

What I need is a formula that can recognise the units the companies 'dividends' and 'repurchases' is in and then divide by the correct exchange rate (that is by the correct entry in the matrix)?