SUMIF, VLOOKUP (for currency conversion on various months)

[mikewray]

Frustrated and cannot get this, no matter which way I try. What formula will give me the EUR amounts for any given month (from cell B3)? See table below...

I dont want to create any more columns etc, I know the long way round...I am looking for an elegant solution. Is sumif, vlookup possible to combine or do I have to use sumif, index, match which I dont really understand. Problem illustrated below, first part is what I want and below is the source data (balances per month in base currency and xchange rate table):

CFFC 0403 v1.xls
	A	B	C	D	E
1	ReportReqd
2
3	Formonth	29-Feb-04
4
5	Category	EUR
6	Bank	formula?
7	Property	formula?
8	Funds	formula?
9
10	Data
11	Company	Category	Currency	29-Feb-04	31-Mar-04
12	FPG	Bank	GBP	18,873	10,255
13	CIO	Bank	GBP	34,748	16,348
14	YTR	Bank	USD	2,654	(6,029)
15	FPG	Bank	GBP	2,232	7,033
16	CIO	Bank	EUR	47,286	18,886
17	YTR	Bank	GBP	13,751	14,237
18	FPG	Property	USD	350,000	350,000
19	FPG	Funds	EUR	29,435	31,234
20	FPG	Property	USD	98,858	102,345
21	YTR	Funds	GBP	120,293	138,907
22
23	XCRates
24	Date	Curr	Rate
25	29-Feb-04	EUR	1.0000
26	29-Feb-04	GBP	0.6686
27	29-Feb-04	USD	1.2493
28	31-Mar-04	EUR	1.0000
29	31-Mar-04	GBP	0.6814
30	31-Mar-04	USD	1.3124
for mr excel

 

[mikewray]

Re: SUMIF, VLOOKUP (for currency conversion on various month

Requirement 1 (closing bal for Cash at 31-3-04) control answer is correct, calculated as follows:

Acct 10 (Cash) = closing Mar bal = GBP18518.08 x 1.49 = EUR27591.94
Acct 20 (Cash) = closing Mar bal = (USD8263.46) x 0.82 = (EUR6776.04)
Acct 40 (Cash) = closing Mar bal = USD18872.76 x 0.82 = EUR15475.66

The total is EUR36291.57. Ah, this is just a rounding error. I had formatted the numbers in my example and did not realise the true value did not carry to the HTML). So I think you got it. Pray tell master, how did you do it?

The difference between the two requirements:
---------------------------------------------------
The first requirement is the closing balance (sum of all transactions to date) of a category at the closing rate.

The second requirement is the movements (transactions) in a particular month for a specified category valued at the exchange rate applicable for that month. So the two are different. It is easy to see for Feb Equity IS, there was only one transaction in Feb, the dividends recd of GBP332 at a rate of 1.52 = EUR504.64

Other than my usual thank yous, how do I make a meaningful contribution to this medium. It is great...

 

[Aladin Akyurek]

Re: SUMIF, VLOOKUP (for currency conversion on various month

Requirement 1 (closing bal for Cash at 31-3-04) control answer is correct, calculated as follows:

Acct 10 (Cash) = closing Mar bal = GBP18518.08 x 1.49 = EUR27591.94
Acct 20 (Cash) = closing Mar bal = (USD8263.46) x 0.82 = (EUR6776.04)
Acct 40 (Cash) = closing Mar bal = USD18872.76 x 0.82 = EUR15475.66

The total is EUR36291.57. Ah, this is just a rounding error. I had formatted the numbers in my example and did not realise the true value did not carry to the HTML). So I think you got it. Pray tell master, how did you do it?

The difference between the two requirements:
---------------------------------------------------
The first requirement is the closing balance (sum of all transactions to date) of a category at the closing rate.

The second requirement is the movements (transactions) in a particular month for a specified category valued at the exchange rate applicable for that month. So the two are different. It is easy to see for Feb Equity IS, there was only one transaction in Feb, the dividends recd of GBP332 at a rate of 1.52 = EUR504.64

Other than my usual thank yous, how do I make a meaningful contribution to this medium. It is great...

mikewray.xls
	A	B	C	D	E	F	G	H
1	DATA
2	Acct	Date	Cmpy	Category	Curr	Description	Amount	Assign
3	10	29-Feb-04	BAL	Cash	GBP	Openingbal29/2/04	18873	BS
4	10	15-Mar-04	BAL	Cash	GBP	PymtOMCinvoices	-332	IS
5	10	31-Mar-04	BAL	Cash	GBP	Bankcharges	-22	IS
6	20	15-Mar-04	BAL	Cash	USD	PymtLARinvoice021/2004	-8382	IS
7	20	31-Mar-04	BAL	Cash	USD	Interest1Q04	119	IS
8	30	24-Feb-04	HFC	Equity	GBP	Openingbal29/2/04	34748	BS
9	30	25-Feb-04	HFC	Equity	GBP	Dividends	332	IS
10	30	29-Feb-04	HFC	Equity	GBP	Purchases	-2220	BS
11	40	29-Feb-04	BAL	Cash	USD	Openingbal29/2/04	18873	BS
12
13	XchangeratestoEUR
14	Period	Curr	Rate
15	29-Feb-04	GBP	1.52
16	29-Feb-04	USD	0.8004
17	31-Mar-04	GBP	1.49
18	31-Mar-04	USD	0.82
19
20			REQUIREMENTS
21			Required1			Required2
22			Enterdate	31-Mar-04		Enterdate	29-Feb-04
23			EnterCategory	Cash		EnterCategory	Equity
24			BalinEUR	36293.51		ISmvmt(EUR)formonth	504.64
Sheet2

Formulas...

D24:

=SUMPRODUCT(--($D$3:$D$11=D23),--($B$3:$B$11<=D22),$G$3:$G$11,LOOKUP($E$3:$E$11,INDEX($B$15:$B$18,MATCH($D$22,$A$15:$A$18,0)):INDEX($C$15:$C$18,MATCH(D22,$A$15:$A$18,1))))

G24:

=SUMPRODUCT(--($D$3:$D$11=G23),--(MONTH($B$3:$B$11)=MONTH(G22)),--(YEAR($B$3:$B$11)=YEAR(G22)),--($H$3:$H$11="IS"),$G$3:$G$11,LOOKUP($E$3:$E$11,INDEX($B$15:$B$18,MATCH(G22,$A$15:$A$18,0)):INDEX($C$15:$C$18,MATCH(G22,$A$15:$A$18,1))))

 

[mikewray]

Re: SUMIF, VLOOKUP (for currency conversion on various month

Exactly...just as as I thought so easy this stuff for you isn't it. Well done, thanks. I really want to get this but I cannot get my head around it and I normally can with this stuff. I get quite a lot of it...the first few bits with the "--" are the criteria being specified, the next is the range where the numbers it should add for those which match the criteria and the last bit is the lookup of the exchange rate.

Questions:
Why are all the arguments seperated by commas - does the comma donate a multiplication or what does it mean. How does it know to add the values in the specified range and then multiply by the appropriate exchange rate, or is it doing it one by one? A normal sumproduct uses the * to seperate the different criteria.

I thought index function was index(array, col, row)...in your index function you have more, or less, depends if you count before after the colon. I suppose explaining the colon will answer this.

I would really like to be able to write this formula fresh but first I need to understand how it is actually performing the math and the syntax that tells it to do it as such. If you dont have time that is ok but I really want to get this and it is killing me not knowing...

Many thanks, Michael