Re: SUMPRODUCT Help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <xlcharlie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:79F96C21-95E5-4B78-8C76-D1873CBF802E@xxxxxxxxxxxxxxxx
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))-SUMPROD
UCT(Price1*Qty))/SUMPRODUCT(Price1*Qty)

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

THANKS!






.

Relevant Pages

  • Re: Greenspan concerned with weak dollar
    ... >> price of oil is affected far more by other factors than the weak dollar. ... affected by other factors than the value of the USD. ... Sure, but thats a relatively small part of total US economy, particularly ... deficit that is almost entirely under the control of the Congress, ...
    (sci.med.dentistry)
  • Re: DOW Soars Past 13,000 ! Bush Economy Booming !
    ... DJIA closing price 12953.94 USD = 9540.57 EUR ... DJIA closing price 10604.27 USD = 11226.74 EUR ... It is stock market price inflation (as opposed to ...
    (alt.politics.bush)
  • Re: OT - English abbreviation question
    ... What about having a currency converter instead? ... My price is set in EUR and I don't want to change it right now, but I was told that it better be given in USD. ... http://www.chembuddy.com - chemical calculators for labs and education ...
    (alt.internet.search-engines)
  • Re: Why Its Pointless To Argue With Global Warming Believers
    ... I have already demonstrated that the price of gold fluctuates relative to the price of other goods. ... Inflation adjustments are made based on the consumer price index, which means that the value in this column directly represents a quantity of CPI goods and services. ... IOW, all of the 'float' in the USD itself has been removed, and what you're seeing what quantity of consumer goods you can buy with a ton of gold. ...
    (comp.sys.mac.advocacy)
  • Re: named range vs text
    ... > For foreign futures the value of a 1pt move is based in their currency ... I have named ranges for each currency - ie Eur = ... > spot Eur price, Jpy = spot Yen price, etc. ... > pulling from bloomberg the base currency which returns as text the base ...
    (microsoft.public.excel.worksheet.functions)