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
ADVERTISEMENT
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
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
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
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
Jul 18, 2006
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies
View Related
Sep 15, 2013
find attached herewith a sample file.
View 4 Replies
View Related
Feb 10, 2007
as you know at the top of the excel 2002 worksheet there are headings:
A B C D E etc etc
how do i change these (A,B,C,D,E) to my own personal heading?
example:
A = Title B = Type C = Subs etc etc
or do i have to 'drop' all the info down by 1 level?
i really need to keep the 'numbers' for the colums so that the 1st entry doesn't start at A2
View 2 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
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
Jul 2, 2009
Is it possible that unless a user enters the value of Z5 into Z6, Excel will not continue, or allow any futher data to be entered, this after a user first selects a institution from a drop down list in H6, which will determine the value of Z5.
View 9 Replies
View Related
Jun 2, 2009
I've got a user form that enters values from a text box into one of the spread sheet columns and a Sum at the top which is not updating when the value is added into the column. After highlighting the cell and pressing return it will update the sum though
I've checked that auto calculation is on and that all cells involved are the same format, I even made up a basic form to simulate the same situation in another workbook and that actually works. Is there any way and code could be causing this trouble? or maybe just a corrupt workbook for some reason?
View 4 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
Dec 2, 2008
I have a userform (FrmComp) and in it i have several Textboxes. When i click on any of the textboxes the calender appears but how i i make the calender assign the date value selected on it to the last clicked on textbox? here's what i have:
View 2 Replies
View Related
Sep 2, 2008
I want to know if there is a function that enters the corrent date automatically. E.g., if I enter "3000" in B1, the result will be "2/9/2008" in, say, B2.
Can it be done?
View 14 Replies
View Related
Mar 22, 2013
I am trying to get a particular cell to have normal dimensions when not within that cell, but once opened, contains a default text preferably within a text box format/size.
View 9 Replies
View Related
Jun 9, 2014
No matter what I do the data entered into the UserForm always goes to the next row that isnt formatted as a table instead of into the the next empty row within the table.
I have tried:
Code:
With Sheet2.Range("B1").EntireColumn
NextRow = .Find(What:="*", _
After:=.Cells(1), _
LookIn:=xlFormulas, _
Lookat:=xlPart, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row + 1
End With
and
Code:
Private Sub CommandButton1_Click()
Dim LastRow As Long
Dim i As Integer, response As Integer
With Sheet1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
[Code] .......
and
Code:
Dim LastRow as LongLastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
and
Code:
Private Sub CommandButton1_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("a65536").End(xlUp)
View 1 Replies
View Related
Mar 14, 2008
I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.
E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1
View 13 Replies
View Related
Jan 13, 2010
I would like a formula (if it is possible) that will list which months occur between two dates;
i.e
Start Date (Cell ref A2) = 01/01/2010 (in the dd/mm/yyy format)
End Date (A3) = 02/05/2010
In cells D2:O2 I have the months Jan-Dec. In cells D3:O3 I would like a "Yes" to appear if the above month occurs between the dates in A2 & A3. In this example would like a "Yes" to appear in cells D3, E3, F3, G3 & H3 but not in the other 'Months' appropriate cells.
View 2 Replies
View Related
Aug 19, 2012
In a cell i need this info when
column a = month
column o = staff member
column m = discount given
if no discount is given column m will show 100%
i need the total of all sales made with a discount i.e not 100% and not blank, in a certain month, by a certain member of staff
step 2: i need the average of all these for each member of staff shown in a different cell
i already have the total sales counted per staff member so this will show me who is doing deals and who is doing the biggest deals.
View 4 Replies
View Related
Sep 19, 2007
i need to delete rows from sheet2 which contains source data for Vlookup on sheet1.
that is i need to run a vlookup for a value in sheet1 and after the value is obtained , delete the row which has that value in the source data......
i am sure a simple for loop will help but it needs to do the events in sequence:
vlookup for row1> fetch / place value (sheet1) > delete data in the source table (worksheet2) > go back to row2 and repeat till the the last.. or till the end of a predefined range..
View 9 Replies
View Related
Oct 23, 2009
WORKSHEET A
COLUMN A
row 1) 1 Jan Paris COLUMN D=1
row 2) 3 feb Berlin COLUMN C= 5
row 3) 16 mar London COLUMN D=1
row 4) 22 apr Paris COLUMN C=2
row 5) 3 jan Rome COLUMN C=4
row 6) 5 apr Paris COLUMN D=3
WORKSHEET B
City Jan Feb Mar Apr
Paris ? ? ? ?
Berlin ? ? ? ?
Rome ? ? ? ?
What kind of formula enters values in Worksheet B instead of the question marks (that is, adds up all the numbers in columns C and D of Worksheet A which happen in the given city and month?)
View 9 Replies
View Related
Feb 26, 2009
My difficulty is this. I have 2 columns, A and B. A contains only 0's and 1's. B contains any number from 1 to 100. I want to count all the instances of any given number in Col B that are matched with a 1 in the corresponding cell in Col A.
View 6 Replies
View Related
Mar 15, 2014
I'm trying to create a master template for doing cash flow projections for a number of years. Ideally I would like have a cell for the number of years I want (10 for example) and have excel populate a group of cells based on that number. Each row of cells would be one year and each column would be one month. The dates would be calculated based on a starting date that I enter into a cell.
After that I would like to have a cell for starting cash flows ($1,000 for example) and then another cell that I can plug increases into. For example cash flows may increase to $1,250. I would also need a cell for the date cash flow increases go into effect (1/2019 for example).
View 1 Replies
View Related
