Range Value: Change The Exchange Rate

Dec 21, 2009

I have a financial sensitivity sheet setup by year, where for example below each year is a cell you can change the exchange rate in and it effects the outcome of cash flow as these cells are linked to part of the finances. Then I have a "reset" button setup that is assigned to a macro. When you click the reset button all cells in the exchange rate row will be changed to a value that is entered in a "base case" cell. That way various years can be changed but also everything can be set back to the default or base case. My macro for that case is this:

View 5 Replies


ADVERTISEMENT

Change SUMPRODUCT To IF With Exchange Rate

Mar 31, 2009

As you can see in the attached sample file, I have different currencies, that all need to be converted to USD.

The exchange rate are displayed on sheet 2.

Currently the formula just picks all values without considering the exchange rate to USD. What I need to have, is a formula which, based on the currency, picks the correct exchange rate and outputs USD.

Apart from that, another thing the formula should consider is the value of G1 in sheet 2. With G1, the user can later define a specific type to be calculated by simply pressing down the button and chose either FG or RM.

The formula needs to:

Find out what currency the value is in and automatically convert it to USD via the exchange rate values. It also needs to consider the value of G1 in sheet 2 in order to filter out the other values. If, for example G1 is FG, then only FG should be taken into the process and therefore only values where type FG is marked.

View 9 Replies View Related

Insert Live Exchange Rate?

Jul 14, 2010

I work with different currencies in my company, now I would to get an up to date state of the cask book. So I have $250, and 500EUR, now I want a formula (connecting to internet) that automatically multiplies the $250 with the current exchange rate, so I know how much I have in Euros in total.

View 5 Replies View Related

USD Equivalent In Column Based On The Exchange Rate At The Time

May 24, 2007

Dates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C

And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.

In Sheet 2 I have a table of the historical exchange rates like this:

DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089

View 9 Replies View Related

EXCEL 2013 :: Show Currency Exchange Rate In One Cell

Apr 16, 2014

Is it possible in Excel 2013 to have one cell show current exchange rate of Euro to dollar?

View 1 Replies View Related

Conditional Divide (probs Occur When I Manually Enters The Exchange Rate)

Sep 25, 2007

The file is a simple sheet which upon entering the actual/Invoice cost (C5), calculates the estimated landing/final cost (C8).

In between the process involves changing the currency from US$ to PKR, make some calculations, and changing back the currency again to US$.

The default rate of currency exchange is set to ave 60 (C12). However if the user knows the current rate he can put it manually in C6 and sheet will make all the calcs on this instead of using the default rate.

Problem:
Everything is working just too perfect. But probs occur when i manually enters the exchange rate.

It does successfully changes the US$ to PKR and calculates everything perfectly but doesnt reverts the final cost back to US$ successfully instead it keeps using the default value instead of user's value

View 7 Replies View Related

Determine The Percentage Rate Of Change In A Trendline

Feb 13, 2009

I'm trying to determine the percentage rate of change in a trendline. I've got a sales chart with 90 days worth of sales figures. I can see that the trendline is trending down, but I'm not sure exactly how much.

If you take the first data point in a trendline and subtract it from the last data point and then divide that by the first it should give you the percent change. Excel must be able to do that math for you but I can't even figure it out to save my life. I can't even figure out how to display the first and last data point much less the "rate" of change I'm looking for.

View 9 Replies View Related

Extract Out Of A Column The Greatest Rate Of Change

Feb 22, 2009

I have wave data and need to extract out of a column the greatest rate of change i.e. start from a1 if the numbers are going down find the first number that starts going upward...and put in column b...then find the next first number that is going down and put in column b....and so on and so on all the way down the column.

View 4 Replies View Related

Compound Rate: Annual Growth Rate %

Jun 4, 2007

The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.

r = 100((Y/X)^(1/n))-1)

So for X = 200, Y = 750, n = 10, we have

r = 100((750/200)^(0.1))-1) = 14.1309%

View 3 Replies View Related

Rate Numbers Based On Numeric Range

Apr 8, 2008

In the attached excel file in column C, beginning on row 4, are numbers. These numbers will automatically update on my bloomberg terminal.

(1) I would like to then in column E to have a rating given to those numbers based on the below scale. The way the scale works is that if its 0.6 its a 10. If its anything slightly above 0.6 exactly, its a 9. A 10 is also a 0.5 and any number in between 0.5 and 0.6. The scale is below.

(2) Given that the formula that is being updated is a bloomberg excel formula (in the attached document I pasted it as values), will an excel formula be able to read the updated number in the bloomberg formula? If not, in column D is there a way to have it automatically paste D as values (and update each time column C changes or workbook is saved), and then have column E read off of those numbers.
Scale:

10 = 0.5 to 0.6
9 = 0.6 to 0.7
8 = 0.7 to 0.8
7= 0.8 to 0.9
6 = 0.9 to 1.0
5= 1.0 to 1.1
4= 1.1 to 1.2
3= 1.2 to 1.3
2= 1.3 to 1.4
1= 1.4 to 1.5
0 = anything greater than 1.5

View 5 Replies View Related

How To Calculate Trend Line Growth Rate (as Annual Percentage Growth Rate)

Feb 13, 2014

From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.

View 6 Replies View Related

First Last Name Exchange

Dec 19, 2007

I have a large employee spreadsheet list with first name then last name in the same cell. Is there a formula that will change the order so it will read last name, first name?

View 9 Replies View Related

Exchange Data Between Cells?

Dec 11, 2012

I wanted to exchange data between two cells. i.e if i have mistakenly put one value of A1 in A2 and A2 in A1. How can I exchange it. I am asking this bec by copy paste method one cell is overwritten so the value of that data is lost.

e.g A1 has value 200 but the correct value is 150
A2 has value 150 but the correct value is 200

How can i exchange/swap the values between the two cells..

View 2 Replies View Related

Exchange Text In A Cell

Sep 4, 2013

Is possible exchange text in a cell like this "street San Miguel de Allende-Cp 4780 031 1304" to "street San Miguel de Allende, 1304 -Cp 4780 031". how to do this?

View 3 Replies View Related

Exchange Data Between Workbooks

Dec 14, 2009

Is it possible when i am in my current workbook to refer to the value in another cell in another workbook within a formula?

Example:

I have a workbook named "Sample1.xls" that contains a spreadsheet "revenue" and in cell C24 is the value that i want to have in my current workbook.

View 2 Replies View Related

Exchange A Number For A Letter Forumla

Aug 8, 2009

I have a sheet which calculates payment amounts.

Column titles:
Hours | Rate of Pay | Total

In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.

What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.

H=2 x rate of pay = total

I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.

View 10 Replies View Related

How To Keep Track Of Changes In Prices Imported From Exchange

Sep 30, 2013

I work with a broker that have an API function and all the prices goes directly to an Excel Sheet.

What I want is a way to keep track of this prices in a new sheet, as they change. Like a historical price.

Is there any way that excel can do it for me?

View 6 Replies View Related

Aligning Two Series Of Currency Exchange Rates With Different Dates?

Nov 28, 2013

I have two different columns of currency time series data with dates that do not align. One trades Monday-Friday, the other seven days a week.

I want to either a) add dates to the shorter series that only trades Monday - Friday (and just use the closing price on Friday for the Saturday and Sunday dates)

b) (not as good an option, as I will lose data) is delete the Saturday and Sunday data from the longer series that trades on the weekend.

View 1 Replies View Related

Post Spreadsheet To Microsoft Exchange Public Folder

Sep 18, 2006

I'm looking at publishing a number of spreadsheets to the Public Folders section on our local Microsoft Exchange Server.

In Excel I can select File > Send To > Exchange Folder...
but I was wondering if there is a way to automate this in a macro?
I would also like to specify a folder it should be posted?

View 6 Replies View Related

Macro To Access A Website And Copy Exchange Rates

Jan 25, 2007

My task is to somehow access [url] and copy the exchange rates on the table to my Excel workbook. Obviously a macro will not record outside of the Excel program, so I am just wondering if this is possible at all (without hacking of course). My plan B is to have the user manually enter the exchange rates.

View 3 Replies View Related

Change To Code For Change In Range

Mar 27, 2012

I have recorded the code below that does what I need, problem is I will use the code on files with various amounts of rows. What can I change in it so it will work on all different size files.

Code:
Sub Macro3()
'
' Macro3 Macro

[Code]....

View 6 Replies View Related

Macro To Change Range / Action Depending On Worksheet (range To Autofill)

Sep 17, 2013

Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data

On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14

I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:

Here is my macro:

Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet

[Code]....

View 8 Replies View Related

Rate Function

Oct 24, 2009

I have pv, fv, and period. there will be no payments made. I want the interest rate. I do it long hand it works. I do it using the rate function it doesn't! This also doesn't work with nper. Here is a simplified excel example. What am I doing wrong?

View 2 Replies View Related

Increasing Rate Of Value?

Mar 27, 2014

I have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.

View 5 Replies View Related

Look Up A Rate Based On Another Value

Oct 15, 2008

I am trying to apply a multiplication factor that would vary depending on the sum of a particular cell. For example let's say the value of cell A5 is the SUM of A1:A4. Let's say the value is 255,000,000. my multiplication factor table looks like this:

1 - 50,000,000.........................(0.17)/1000
50,000,001 – 100,000,000..........(0.13)/1000
100,000,001 – 250,000,000.........(0.10)/1000
250,000,001 – 500,000,000.........(0.08)/1000
500,000,001 – 750,000,000.........(0.07)/1000
750,000,000 ++ .......................(0.05)/1000

So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. I'm trying to avoid manually inputting the (0.XX)/1000 based on the formula identifying which range the sum falls into and automatically applying the appropriate multiplication factor. In the example above the formula would be applied to the A6 cell and identify the A5 sum as falling into the 250,000,001 - 500,000,000 "range" and automatically apply the corresponding multiplication factor.

View 5 Replies View Related

RATE FORMULA

Aug 23, 2007

I would like to know the formula used in excel for rate function. Anybody knows and can share with me ? as i need to use that in other programmining language where these financial functions are not available.

View 9 Replies View Related

Last Purchased Rate

Dec 27, 2009

A B C D 1 Date code price 2 10/09/08 abc101 1100.00 3 12/10/09 abc104 1255.00 4 30/08/09 abc101 1389.00

In D1 I want the last purchased rate of a code ( suppose here it is abc101, but it would be different for other item). Here Date is dd-mm-yy.

View 9 Replies View Related

Populate Rate Table

Aug 18, 2009

In Table A, I have the rate table for an insurance product. I need to build a formula that will outomatically populate the data in Table B based on plan, start age and deductibles.

Example, if a person selects age 32 years, deductibles as 0 and plan 4, then his yearly premium will be $750/12=$62.50.This will automatically increase when his age falls into the next age bracket which is 36-45 years and his premium at age 36 to 45 will be $970/12=$80.83.

The Table B should populate all this information from the start age to age 80 when the policy stops. An example of table B is shown in the attachement for someone who starts at age 32, chooses deductibles as 0 and plan 4.

View 2 Replies View Related

Sales Run-rate. Too Tough For Me

Feb 4, 2009

I'm trying to calculate a sales run-rate which will change on a day-to-day basis, to predict the end-of-month sales total.

The invoice values are in the data range H17:H74 (I don't want the final formula to add up the refunds in this field i.e. negative values)

The date field is in data range C17:C74.

So basically the formula will need to add all invoice totals (excluding refunds) and divide by the current number of days worked in the month (not duplicating days in the date field) and then multiply this by the average number of working days in a month (21). This should should give a predicted end of month sales total.

Am I just making things up that are impossible to do on excel?

I've had a bet with the other guy in the sales office because I said excel can do a lot more than he thinks. He's under the impression that excel begins and ends with what they teach you at school.

View 12 Replies View Related

Interest Rate Function

Mar 6, 2007

Is there a function that calculates what interest rate is needed to turn a $2000 investment into $9000000 in 30 years based on compounding interest?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved