I replied to Topper as to why the IF test is not redundant. It is actually
necessary when you consider the second to last row of the example set where
both price 1 and price 2 are EUR, but the FX rate is not 1 because FX rate
reflects the conversion rate to USD. Price 1 and Price 2 are quotes from two
sources at two different points in time. They are different currencies
because they are foreign securities and one source quotes them in the local
foreign currency while the other source quotes them in the USD equivalent.
Hopefully that clarifies it.

"Bob Phillips" wrote:

Toppers made the point that the IF test is redundant, but I don't see where
Price 1 and Price 2 comes into it, and why they would be different
currencies. As far as I can see, there is one calculated price dependent on
the unit price, the rate and the quantity.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"xlcharlie" wrote in message
...
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<Price2C urrency,FXRate,1))-SUMPROD
UCT(Price1*Qty))/SUMPRODUCT(Price1*Qty)

but I am getting an error. Anyone know how to make this work?

THANKS!