X
xlcharlie
I have a table of prices like the following:
Currency Currency FX Rate Price 2 Quantity
Price 1 Price 2
USD USD 1 38.61 500
USD USD 1 36.95 718
CAD USD 1.114 74.11 325
GBP GBP 0.56930 19.72 900
EUR EUR 0.7996 23.34 602
EUR USD 0.7996 49.87 400
Some of the time Price 2 is quoted in the same currency as Price 1, but if I
want to get the total cost of each record (price*qty) in the same currency as
Price 1, I have to multiply by the FX Rate whenever the two currencies aren't
the same. So the equation I am using is
=Quantity*Price*IF(Currency1<>Currency2,FX Rate,1).
Next, I want to calculate the percent change in value of all the records as
a portfolio. So for one record, (Price2-Price1)/Price1 = % Change in value,
but I cannot just add the % change of all the records to get the total change
in value of the portfolio. I believe I can do it with SUMPRODUCT, but if I
use
=(SUMPRODUCT(Price2*Qty)-SUMPRODUCT(Price1*Qty))/SUMPRODUCT(Price1*Qty)
I neglect to convert Price2 to the same currency as Price1 when the two
differ. (When they do not differ, no conversion is necessary to get an
accurate percent change.) I tried embedding something like
=(SUMPRODUCT(Price2*Qty,IF(Price1Currency<>Price2Currency,FXRate,1))-SUMPRODUCT(Price1*Qty))/SUMPRODUCT(Price1*Qty)
but I am getting an error. Anyone know how to make this work?
THANKS!
Currency Currency FX Rate Price 2 Quantity
Price 1 Price 2
USD USD 1 38.61 500
USD USD 1 36.95 718
CAD USD 1.114 74.11 325
GBP GBP 0.56930 19.72 900
EUR EUR 0.7996 23.34 602
EUR USD 0.7996 49.87 400
Some of the time Price 2 is quoted in the same currency as Price 1, but if I
want to get the total cost of each record (price*qty) in the same currency as
Price 1, I have to multiply by the FX Rate whenever the two currencies aren't
the same. So the equation I am using is
=Quantity*Price*IF(Currency1<>Currency2,FX Rate,1).
Next, I want to calculate the percent change in value of all the records as
a portfolio. So for one record, (Price2-Price1)/Price1 = % Change in value,
but I cannot just add the % change of all the records to get the total change
in value of the portfolio. I believe I can do it with SUMPRODUCT, but if I
use
=(SUMPRODUCT(Price2*Qty)-SUMPRODUCT(Price1*Qty))/SUMPRODUCT(Price1*Qty)
I neglect to convert Price2 to the same currency as Price1 when the two
differ. (When they do not differ, no conversion is necessary to get an
accurate percent change.) I tried embedding something like
=(SUMPRODUCT(Price2*Qty,IF(Price1Currency<>Price2Currency,FXRate,1))-SUMPRODUCT(Price1*Qty))/SUMPRODUCT(Price1*Qty)
but I am getting an error. Anyone know how to make this work?
THANKS!