function? :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

function?
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

function?

Hi,

I frequently need to convert currency form Euro to US Dollar, and from USD to Euro. What would be the best way to create 2 formulas or functions, either using VBA or not, that I could use from any open workbook, so always accessible, to perform those currency conversions? Something like:

1000 * (USD_TO_EURO), where the input (1000) is in USD and the result in Euro, and also the other way around. Of course the "input" would be a value taken from any cell in the workbook.


Thank you in advance for any help.
Best Regards,
MrDoc

Post Tue Nov 11, 2003 12:42 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8267

Flag: Uk

Status: Offline

 Reply with quote  

Re: function?

How would you determine the exchange rate, which changes all the time?

Post Tue Nov 11, 2003 1:09 pm 
 View user's profile Send private message

al_b_cnu
Board Master


Joined: 18 Jul 2003
Posts: 724
Location: Manchester (UK)
Flag: Uk

Status: Offline

 Reply with quote  

Hi,

Have you seen the EUROCONVERT XL function?

HTH

Alan

Post Tue Nov 11, 2003 1:18 pm 
 View user's profile Send private message Send e-mail

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

Re: function?

Hi Andrew,

The exchange rate would be updated in a cell in the worksheet, so it wouldnīt really matter to the function/formula. I also often need to convert from euro to former portuguese currency (escudo) and back, and in this case there is a fixed value: 1 Euro = 200,482 escudos. It never changes.
Thank you for your reply,
MrDoc

Post Tue Nov 11, 2003 1:22 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8267

Flag: Uk

Status: Offline

 Reply with quote  

quote:
Originally posted by al_b_cnu:
Hi,

Have you seen the EUROCONVERT XL function?

HTH

Alan


That only works for members of the Euro countries, where there are fixed exchange rates.

Post Tue Nov 11, 2003 1:22 pm 
 View user's profile Send private message

al_b_cnu
Board Master


Joined: 18 Jul 2003
Posts: 724
Location: Manchester (UK)
Flag: Uk

Status: Offline

 Reply with quote  

quote:
Originally posted by Andrew Poulsom:
quote:
Originally posted by al_b_cnu:
Hi,

Have you seen the EUROCONVERT XL function?

HTH

Alan


That only works for members of the Euro countries, where there are fixed exchange rates.

I wondered why there was no exchange rate parameter.

Thanks for that

Alan

Post Tue Nov 11, 2003 1:24 pm 
 View user's profile Send private message Send e-mail

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

Re: function?

Hi Alan,

The EUROCONVERT function would probably solve some of my problems, but I canīt find it in my Excel (2002 or XP).
Thank you for replying,
MrDoc

Post Tue Nov 11, 2003 1:26 pm 
 View user's profile Send private message

al_b_cnu
Board Master


Joined: 18 Jul 2003
Posts: 724
Location: Manchester (UK)
Flag: Uk

Status: Offline

 Reply with quote  

Re: function?

quote:
Originally posted by MrDoc:
Hi Alan,

The EUROCONVERT function would probably solve some of my problems, but I canīt find it in my Excel (2002 or XP).
Thank you for replying,
MrDoc


Perhaps it has now been withdrawn (I've got Excwl 2000)

Alan

Post Tue Nov 11, 2003 1:28 pm 
 View user's profile Send private message Send e-mail

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8267

Flag: Uk

Status: Offline

 Reply with quote  

Re: function?

It's in the Euro Currency Tools Add-In (Tools|Add-ins).

For the US Dollar, just divide the dollars by the exchange rate.

Post Tue Nov 11, 2003 1:30 pm 
 View user's profile Send private message

fairwinds
Board Master
Board  Master


Joined: 15 May 2003
Posts: 1237

Flag: Sweden

Status: Offline

 Reply with quote  

Re: function?

Well what I do is to define different names.

Insert - Name - Define referring i.e EUR-USD to the appropriate cell with the exchange rate and then use that in my calculations. I don't think any functions could make that easier.
_________________
"Fair Winds and Following Seas"

Post Tue Nov 11, 2003 1:39 pm 
 View user's profile Send private message

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

Re: function?

That's what I do now, but I canīt use the names from any workbook, so I have to define the names in each workbook. Or can I?...

As to the EUROCONVERT, I installed the EUROTOOL add-in, but nothing happened when I selected EUROTOOL CONVERSION from the TOOLS menu. Can you please tell me what exactly are the EUROCONVERT() parameters?

Thank you for your help.

Post Tue Nov 11, 2003 2:02 pm 
 View user's profile Send private message

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

Re: function?

That's what I do now, but I canīt use the names from any workbook, or can I....?
I installed the EUROTOOL addi-n, but when I click EUROCONVERSION on the TOOLS menu nothing happens. Can you please tell me exactly what parameters must I use in the EUROCONVERT() function?

Thank you for your help.
MrDoc

Post Tue Nov 11, 2003 2:12 pm 
 View user's profile Send private message

al_b_cnu
Board Master


Joined: 18 Jul 2003
Posts: 724
Location: Manchester (UK)
Flag: Uk

Status: Offline

 Reply with quote  

Re: function?

From the Help file:

Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation). The currencies available for conversion are those of European Union (EU) members that have adopted the euro. The function uses fixed conversion rates that are established by the EU.

Syntax

EUROCONVERT(number,source,target,full_precision,triangulation_precision)

Number is the currency value you want to convert, or a reference to a cell containing the value.

Source is a three-letter string, or reference to a cell containing the string, corresponding to the ISO code for the source currency. The following currency codes are available in the EUROCONVERT function:

Country Basic unit of currency ISO code
Belgium franc BEF
Luxembourg franc LUF
Germany deutsche mark DEM
Spain peseta ESP
France franc FRF
Ireland pound IEP
Italy lira ITL
Netherlands guilder NLG
Austria schilling ATS
Portugal escudo PTE
Finland markka FIM
Euro member states euro EUR



The following countries may adopt the euro after the initial version of EUROCONVERT, and if so, Microsoft will update the EUROCONVERT function. For information about new euro member currencies and updates to the EUROCONVERT function, connect to the Microsoft euro Web site.

Country Basic unit of currency ISO Code
Denmark krone DKK
Greece drachma GRD
Sweden krona SEK
UK pound sterling GBP


Target is a three-letter string, or cell reference, corresponding to the ISO code of the currency to which you want to convert the number. See the previous Source table for the ISO codes.

Full_precision is a logical value (TRUE or FALSE), or an expression that evaluates to a value of TRUE or FALSE, that specifies how to round the result.

Use If you want Excel to
FALSE Use the currency-specific rounding rules, see the table that follows. Excel uses the calculation precision value to calculate the result and the display precision value to display the result. FALSE is the default if the full_precision argument is omitted.
TRUE Ignore the currency-specific rounding rules and instead use the six-significant-digit conversion factor with no follow-up rounding.



The following table shows the currency specific rounding rules, that is, how many decimal places Excel uses to calculate a currency's conversion and display the result.

ISO code Calculation precision Display precision
BEF 0 0
LUF 0 0
DEM 2 2
ESP 0 0
FRF 2 2
IEP 2 2
ITL 0 0
NLG 2 2
ATS 2 2
PTE 1 2
FIM 2 2
EUR 2 2


Triangulation_precision is an integer equal to or greater than 3 that specifies the number of significant digits to be used for the intermediate euro value when converting between two euro member currencies. If you omit this argument, Excel does not round the intermediate euro value. If you include this argument when converting from a euro member currency to the euro, Excel calculates the intermediate euro value that could then be converted to a euro member currency.

Remarks

Excel truncates any trailing zeros in the return value.


If the source ISO code is the same as the target ISO code, Excel returns the original value of the number.


Invalid parameters return #NUM.


This function does not apply a number format.
Examples

These examples assume conversion rates of 1 euro = 6.55957 French francs and 1.95583 deutsche marks. The EUROCONVERT function uses the current rates established by the EU. Microsoft will update the function if the rates change. To get full information about the rules and the rates currently in effect, see the European Commission publications about the euro. For information about obtaining these publications and updates to the EUROCONVERT function, connect to the Microsoft Office euro Web site.

The examples show the resulting value stored in the cell, not the formatted value.

EUROCONVERT(1.20,"DEM","EUR") equals 0.61 euro. Because neither full_precision nor triangulation_precision are specified, the result uses the calculation precision for the euro, which is 2 decimal places.

EUROCONVERT(1,"FRF","EUR",TRUE,3) equals 0.152 euro. When converting to euros, if full_precision is TRUE, the result has the precision specified by triangulation_precision.

EUROCONVERT(1,"FRF","EUR",FALSE,3) equals 0.15 euro. When converting to euros, if full_precision is FALSE, the result uses the calculation precision for the euro, which is 2 decimal places.

EUROCONVERT(1,"FRF","DEM",TRUE,3) equals 0.29728616 DM. Because triangulation_precision is 3, the intermediate euro value is rounded to three places. Because full_precision is TRUE, the resulting deutsche mark value is stored with all significant digits.

EUROCONVERT(1,"FRF","DEM",FALSE,3) equals 0.30 DM. Because triangulation_precision is 3, the intermediate euro value is rounded to three places. Because full_precision is FALSE, the resulting deutsche mark value is rounded to the calculation precision for the deutsche mark, which is 2 decimal places.

HTH

Alan

Post Tue Nov 11, 2003 2:12 pm 
 View user's profile Send private message Send e-mail

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8267

Flag: Uk

Status: Offline

 Reply with quote  

Re: function?

Look up the EUROCONVERT function in Help for the arguments.

Are you keeping the exchange rate in only one workbook and you would like to reference it? What is the name of the workbook?

Post Tue Nov 11, 2003 2:13 pm 
 View user's profile Send private message

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

Re: function?

Thank you again, Alan and Andrew. However, there must be something wrong with my EUROTOOL add-in, because Excel keeps crashing everytime I try to use it.

So, if I can use defined ranges from one workbook, I'll create a workbook and name it, say, "MyRates". What I need now is to be sure of how to refer to that workbook from any workbook and also how to make the necessary conversions according to the official rule (2 decimal places, the 3rd decimal place will define which way to round the result value, so 200.482 = 200.48 and 200.486 = 200.49).
Best Regards,
MrDoc

Post Tue Nov 11, 2003 2:27 pm 
 View user's profile Send private message

al_b_cnu
Board Master


Joined: 18 Jul 2003
Posts: 724
Location: Manchester (UK)
Flag: Uk

Status: Offline

 Reply with quote  

Re: function?

Hi,

Set up a workbook, say 'c:\temp\rates.xls with defined names 'Rate1' and 'Rate2' etc. containing respective conversion rates.


In your workbook
=A1*'C:\Temp\rates.xls'!Rate2

HTH

Alan

Post Tue Nov 11, 2003 2:41 pm 
 View user's profile Send private message Send e-mail

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8267

Flag: Uk

Status: Offline

 Reply with quote  

Re: function?

Create a workbook holding the exchange rate in cell A1 and name the cell eg USDRate. Save the workbook as eg ExchRate.xls.

In any other workbook you can access it like this:

=A1*'C:\ExchRate.xls'!USDRate

changing the path (mine is C:\) if necessary. If you want you can define a range named eg USDRate in the other workbook, referring to:

='C:\ExchRate.xls'!USDRate

Then you can use just:

=A1*USDRate

Post Tue Nov 11, 2003 2:43 pm 
 View user's profile Send private message

MrDoc
Board Master


Joined: 31 Jul 2002
Posts: 221
Location: Portugal
Flag: Portugal

Status: Offline

 Reply with quote  

Re: function?

Andrew, Alan,

It's done, thanks a lot for your help!

Best Regards,
MrDoc

Post Tue Nov 11, 2003 4:04 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.