Alternative to IF statements (Co nvert currencies…)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin Kraft - 14 Nov 2006 16:39 GMT

Right now I have a number of IF statements as part of my work sheet. I use it
to look up the applicable currency for the row and then do the conversion. I
think it is too much calculating for EXCEL 2007 to do.

Any ideas? Thoughts? Comments?

Here is my IF statement:

=IF($B5="EURO",G5*$U$3,
IF($B5="CHF",G5*$U$4,
IF($B5="CZK",G5*$U$5,
IF($B5="ARS",G5*$U$6,
IF($B5="AUD",G5*$U$7,
IF($B5="BGN",G5*$U$8,
IF($B5="CNY",G5*$U$9,
IF($B5="CYP",G5*$U$10,
IF($B5="DKK",G5*$U$11,
IF($B5="GBP",G5*$U$12,
IF($B5="HKD",G5*$U$14,
IF($B5="IDR",G5*$U$15,
IF($B5="ILS",G5*$U$16,
IF($B5="JPY",G5*$U$18,
IF($B5="MYR",G5*$U$19,
IF($B5="NOR",G5*$U$21,
IF($B5="NZD",G5*$U$22,
IF($B5="PLN",G5*$U$23,
IF($B5="SEK",G5*$U$26,
IF($B5="SGD",G5*$U$27,
IF($B5="SKK",G5*$U$28,
IF($B5="THB",G5*$U$29,
IF($B5="TRY",G5*$U$31,
IF($B5="USD",G5*$U$32,
IF($B5="VEB",G5*$U$33,
IF($B5="VND",G5*$U$34,
IF($B5="ZAR",G5*$U$36,
)))))))))))))))))))))))))))

Reply to this Message

Arvi Laanemets - 14 Nov 2006 16:59 GMT

Hi

Create a new sheet Coefficients, and there the table (headings in A1:B1)
Currency Coefficient
USD 1
EURO ?
...

I.e. you´ll have convesion coefficients on sheet Coefficients instead of
column U - and preceeded with currency abverrations.

When you may have to add new currencies into this table, then it'll be
clever to define it as named range (Insert > Name > Define), like
CoeffTbl=OFFSET(Coefficients!$A$1,1,,COUNTA(Coefficients!$A:$A)-1,2)

Now your example formula will be
=G5*VLOOKUP($B5, CoeffTbl, 2,0)
Without defining any named range
=G5*VLOOKUP($B5, Coefficients!$A$2:$B$30, 2,0)

Arvi Laanemets

> Right now I have a number of IF statements as part of my work sheet. I use it
> to look up the applicable currency for the row and then do the conversion. I
[quoted text clipped - 32 lines]
> IF($B5="ZAR",G5*$U$36,
> )))))))))))))))))))))))))))

Reply to this Message

aaron.kempf@gmail.com - 14 Nov 2006 21:14 GMT

that is f.cking hilarious

use a database, jackass

-Aaron

Reply to this Message