Currency code - complication
hb-timm
Is their anyone who can help me in, for me a complicated formel...
I have the following setup
in a5.. the figure 3
in b5..the figure 2
in c5..a letter e.g. D...(D...for Denmark)
in d5..500 (which is an amount)
in f5..0,00
in f6..7,45 (which supposed to exchange rate from DKK to EURO)
in f7..6,36 (supposed to be exchange rate from DKK to USD)
in f8..10,10 (supposed to be exchange rate from DKK to GBP)
I need a formel in box e5, where a5 is pultiplied with b5, multiplied
with d5, using the indicator in box c5 to pickup the exchange rate
in one of the boxes f5 to f8.
I have tried with: =((a5*b5*d5)*Hvis(C5="E";!F6;0))
but this not right...
The ider is the using the capital letter e.g. D for Danish Kroner, E for
Euro,
U for USD and G for GPB...in that way that the capital letter in box c5
would take the amount from the respective box of f5 to f8...
The currency code in c5 should reflect the amound in d5.
hope anyone can help me
best regards
Bjarne E. Timm
koun...@mbh.nifty.com
"hb-timm" <hb-...@mail.tele.dk> wrote in message
news:3fa16b02$0$69912$edfa...@dread12.news.tele.dk...
>
> I have the following setup
>
> in a5.. the figure 3
> in b5..the figure 2
> in c5..a letter e.g. D...(D...for Denmark)
> in d5..500 (which is an amount)
> in f5..0,00
> in f6..7,45 (which supposed to exchange rate from DKK to EURO)
> in f7..6,36 (supposed to be exchange rate from DKK to USD)
> in f8..10,10 (supposed to be exchange rate from DKK to GBP)
>
> I need a formel in box e5, where a5 is pultiplied with b5, multiplied
> with d5, using the indicator in box c5 to pickup the exchange rate
> in one of the boxes f5 to f8.
> I have tried with: =((a5*b5*d5)*Hvis(C5="E";!F6;0))
> but this not right...
>
i don't know how the function Hvis() works. so, i will take another
approache.
first of all, make a exchange rate table containing country code and
exchange rate in anywhere you like. e.g. in range G5:H8. let G5 to G8 be
country code, e.g. D in D5, E in G6, U in G7, G in G8. let H5 to H8 be
exchange rate, e.g. exchange rate from DKK to DKK in H5, exchange rate from
DKK to EURO in H6, exchange rate from DKK to USD in H7, exchange rate from
DKK to GBP in H8. next using function match(), find country code's position
in the table, then using function index(), you can find exchange rate in the
table.
so, formula in e5 is like this
=(A5*B5*D5)*INDEX(G$5:H$8,MATCH(C5,G$5:G$8,0),2)
does this works?
or if it's not the result what you want. ignore this article.
sorry for my poor English.
p.s.,
i think exchange rate which you discribe above seems to be incorrect to me.
for example, if exchange rate from DKK to GBP is 10,10, i think it meams
that 1DKK=10,10GBP. but the relation between GKK and GBP is
1DKK=(1/10,10)GBP -> 1GBP=10,10DKK, i think. my misunderstanding? then very
sorry.
--
******************************
keizi kounoike
******************************