How to convert currencies in whole sheet

English
#4

If you are in Writer, your chances are slim. Writer doesn’t have powerful formulas for calculating. If you are working in Calc, which makes more sense, you can of course use a simple formula to do the conversion - if you convert dollars to pounds, multiply the values in dollars by the exchange rate. If you have the values in the C column, you’d get something like

=C2*0.73

Of course, that will only work if you didn’t include the dollar signs in the cell content but in the formatting. If you have C2 = “$1234.56”, formatted as text, you first have to delete the dollar signs, then format the cells as currency before you can do the math.

1 Like
#5

First of all, I must confess to you that I am a layman in professional financial matters.
But BPS is not an official designation, use GBP instead.
But maybe I’m wrong, and in other countries this designation is as you indicate.
You can search the internet for “currency converters” that will do a conversion and give you the correct denomination of each currency.

Here is an example from the “Bankenverband Germany”:

05-03-_2022_21-38-42 Bankenverband Germany
05-03-_2022_21-38-42 Bankenverband Germany1261×880 107 KB


As an expert, you will know that the conversion rate is volatile and can change.
IMHO you don’t need a special function for the conversion, it’s a simple calculation as shown in the screenshot above.
In Calc simple calculations can be done optimally.

Basic arithmetic operations and simple functions in Calc

However, if you imagine a conversion of, for example, columns, with arbitrarily long value series, this could IMHO only be done with a macro or programming.
Both macros and programming are not my subject area.

I hope I could help you with my sparse knowledge about it, something further.

1 Like
#6

right, I am working in Calc/Excel and thought there might be a string like IF something…) or REF to a block of cells with all currency formats converts to one or the other, but its probably requires writing a Python command string to get it working. DOnt have the Python chops yet so I suspect I have to do a column reference to all the numbers in the same column and do a reference to it in case US$ instead of Euro is required. thanks for the input though.

#7

thank you, yes, it requires at least a macro and maybe some python coding to do what I am trying to do, which is pretty fancy but though I’d try it anyway. The currency rate change can be referenced to an outside cell which takes care of the fluctuations, but in reality, the changes in the currency over a year or two is minor and is adjusted every three months by contract anyway. In any case, will work on the macro. Thanks for your input.

#8

You don’t need a macro to copy a formula for a range of cells. If you have your amounts in dollars in C2-C100, you can put the conversion formula in D2, then copy that, select the cells D2-D100 and Paste. The cell numbers will be updated automatically.

1 Like
#9

See the comment from @anon87010807

05-03-_2022_22-24-16 USD-GBP Umrechnung

Enter the formula and drag the small square at the bottom right corner downwards.
You can start dragging again at the last position with a formula.

How to convert currencies 74885.ods (19,5 KB)

#10

right, thanks, but if you have 12 tabs with 2 hundred cells each calculating in different ways, the aim is to get each cell that has a “$” sign in it to convert to BPS£ using current multiplyer reference for BPS. So it should all revert back to one cell that either uses $ or E and when you type in the cell reference of those cells, all other numbered cells will change. it case some planning

#11

You should have written that in your first post, that would have saved us, and you, some time. If you really want to replace the values in dollars in each cell by the conversions to pounds, you will need a macro, because formulas in Calc (and Excel) can’t affect the cell that contains them (okay, there’s limited self-reference, but you don’t want that).

#12

everybody makes assumptions about what other people do or do not know. Is this case I made the assumption that the question requires some kind of formula and simple macro syntax because it isn’t a very complicated question, you just have to know what language to use in the formula to get the result. If you feel I wanted your valuable time, maybe you should do spend your time doing something else.

#13

That’s brave in a collaborative world like business, someone will always ask a question relating to the original data. After overwriting original data with a calculated number in a macro you cannot use exchange rate any more because the original data has gone, you then have to use the percentage difference between original exchange rate and current exchange rate after that. It gets more complicated for third exchange rate change.

Better to leave original data and show calculations from that. Create a named range of one cell and call it, say, USDx (and maybe one EURx) and enter the current exchange rate in that. Then just create parallel columns and in that enter something like =A2*USDx then do further calculations from that result. If the exchange rate changes you can do a Save a Copy to keep record of previous rate calculations then enter the new exchange rate in USDx all cells will update to reflect new rate.

#14

yes, your suggestion is a good one. The contracts are a one time estimate for project cost and wont change although the long-term contracts will but will only one adjusted once every quarter and options will hedge any fluctuation so thats less of a concern. My attempt wa to elevate the functionality and the code to where the command would solve the problem of changing al the numbers in the spreadsheets as opposed to just the final project cost. I appreciate your message, though, and think that someone will benefit from your wisdom…

#15

Assuming that 0.73 is the exchange rate, copy this value, search for all USD in cells, and paste special (Ctrl+Shift+V) marking operation Multiply, change format to Euro, and save as a copy.

This will add “*0.73” to all formulas, and change the numbers for the answer. You can play a bit to multiply for 999999999, then replace *999999999 for a reference to the cell with the exchange rate value (i.e., B3). So, if in B3 it says 0.73 you will have the values in Euro, and if it says 1 in USD. You can add conditional formatting: if 1 then USD, if not Euro.

replace currency
replace currency1071×515 56.2 KB

Tested with LibreOffice 7.1.8.1.

EDIT:
Version: 7.1.8.1 (x64) / LibreOffice Community
Build ID: e1f30c802c3269a1d052614453f260e49458c82c
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: es-MX (es_ES); UI: en-US
Calc: CL

2 Likes
#16

I learned something today, thank you!

#17

are you using a MS Excel on a PC? I am not getting the same options

#18

@LeroyG , which operating system are you working with?

In Windows 10, the “Paste Special” dialog is not offered. :confused:

#19

It is at Edit > Paste Special > Paste Special (or Ctrl+Shift+V) .

LO 7.2.5.2 dialogue box layout is slightly different but same buttons

#20

FYI, I discovered that the suggested command menu is in fact included in the MacExcel and Libre Calc software, too, although a bit less sofisticated. Your suggested to name a range and go from there sounds like something I am going to try although the numbers in the financials are all over the place and the challenge will be to get all the numbers with a “$” tro convert and not the others…anyways, thanks a lot for the help, appreciate it.

#21

I got it to work, much appreciated!

#22

How are you copying? Copy the cell (with value 0.73), not the cell content.

#23

that’s one way of doing it. if you then do an external link to a live currency quote it automatically updates all the numbers…

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.