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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
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
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
Apr 8, 2009
Change Sumproduct to countif(and(. I'm using this formula
View 2 Replies
View Related
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
Mar 3, 2014
I have have sumproduct formula that works well, however I need a section of it to use a condition.
"=SUMPRODUCT(--(('Input Sheet'!$A$10:$A$20009=3)+('Input Sheet'!$B$10:$B$20009=1)+('Input Sheet'!$C$10:$C$20009=1)=3),--('Input Sheet'!$L$10:$L$20009>=G21),--('Input Sheet'!$L$10:$L$20009<=J21))"
The first section ('Input Sheet'!$A$10:$A$20009=3) looks at column of data and checks if any of the data says 3, however I need the formula to look at the column of data and determine if the figure is =>3 and also =<5.
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
May 12, 2007
FromToGr Rate
90100A14.0
8689A23.6
8085B13.2
7279B23.0
6471B32.8
5663C12.6
This is part of a marks to grade and rating table. I have a list of students and the marks they obtained in an exam. I need a formula to put the grade and rate beside each name e.g. John; 92 marks; A1; 4.
View 9 Replies
View Related
