Calculate FX P&L Based On Quoted Currency
Aug 24, 2009
I am trying to work out FX P&L by linking trades recorded on a spreadsheet to FX rates depending on which way the currency is quoted. I have attached my spreadsheet to the post. In the Worksheet 'Daily' In Column L, I want to calculate the USD Equivalent for all the trades recorded, by perfoming a simple calculation linked to the FX Rates in the 'Summary' Worksheet.
If on a trade, the Accounting Currency (P&L Currency) Is USD, then I just want the cell to equal the amount in column J. If however the P&L Currency is any other currency, I want it to divide the amount in that field to the corresponding rate in the 'Summary' Worksheet. For example, in Row 108, on the Daily Worksheet, the P&L currency is JPY. I want to divide the JPY amount in J108 by the JPY fx rate in the 'Summary' Worksheet and return that number in L108, so the answer would be $59,353.47.
View 2 Replies
ADVERTISEMENT
Mar 11, 2012
How to make EXCEL 2007 Automatically convert all my CURRENCY symbols in all cells formatted for CURRENCY in THE SAME SHEET to the one and ONLY currency selected in JUST ONE cell ? (i mean, after conversion, all of them are Not just display currency symbols, but Calculable monetary values like regular Excel's built-in currency format)
I am a dummy in VBA so I really am not familiar at all if the solution is really one of VBA. I don't even know how to start programming VBA at all in Excel.
View 3 Replies
View Related
Apr 23, 2009
I have several rows which lists different currencies - USD, GBP, EURO, JPY etc.,
A1 usd 5000
A2 GBP 7000
A3 USD 3400
A4 EUR 12000
A5 JPY 89000
A6 EUR 1000
At the bottom I want to show the total of each currency separtely.
View 13 Replies
View Related
May 12, 2014
I have a data set of Purchase Order information (each PO on one line) that spans from 2013-YTD and includes the following fields.
P.O. Amount
P.O. Issue Date
P.O. Close Date
I want to know how much P.O. Amount was "Issued" in the same week in which it was "Closed".
I would like to derive the following from that information:
Average of P.O. Amount that is Issued and Closed in the same week (in % and $ terms),
Count of P.O. Amount Issued and Closed in the same week.
I don't know how to aggregate the information to know how much P.O. Amount was Issued and Closed in the same week.
View 2 Replies
View Related
Mar 3, 2009
I have a list of sales made during a certain period. They are either in £ or $ and are in a list. Essentially like this (but with lots more info):
sale1 £300
sale2 $450
sale3 £150
etc...
What I would like to do is to have two cells at the top which sum only the £ values and a cell whcih sums only the $ values. Is this possible?
My idea to was do a sumif formula based on the cell format of the sales value, but I can't see a way of doing this. The only other way I can see is to have a simple addition formula selecting only the certain cells i want, but this would be labour intensive to maintain, as the formula would need to be updated eachtime a new line is added.
View 9 Replies
View Related
Jan 6, 2010
I have a conditional formatting issue in Excel 2007.
I have a drop down list which shows 4 countries; UK, DE, FR and NL
I would like to change the format of a number of cells to £ or € dependent on what the dropdown box has chosen.
I have a number of vlookups, so it pulls the right amount, and the right SKU, but I cannot get it to change the formatting correctly. I have even tried to simplify it to say, if UK use £ and if <> UK then use €, but that doesn't change either. If I change it, so it colours red for UK, and clear for not UK, that works.
View 12 Replies
View Related
Feb 28, 2014
What I am trying to do is create a multi-currency expense report. For example, cell B1 has a drop down list of three different currencies. Based on the currency selected in B1, I want C1 to reflect that number formatting using the proper currency.
Im sure there is an easy way to do this but I have tried using an if statement in the conditional formatting section but it does not work.
how to total the different currencies using the ifsum formula, so this is more of a presentation task, but still it has annoyed me to no end.
In the example workbook, the currency value selected in row E should effect the currency formatting in Rows F-L
View 1 Replies
View Related
Feb 26, 2008
I am struggeling to format a column to display currency values formated with a different currency mark than the local system currency. Right now, when I run the macro on my machine I get my local currency (Israely Shekel), and on the Client's machine I get Euro. (I need it to be in dollars)
View 6 Replies
View Related
Dec 30, 2013
I have a spread sheet which totals the amounts in 2 columns D and E, which are pounds and pence. The formulas for each one are:
Pounds
=SUM(D3:D8)+ROUNDDOWN((SUM(E3:E8)/100),0)
Pence
=MOD(SUM(E3:E8),100)
These work very well and give me the correct figures.
What I would like to do is to add up the values of each row based on the criteria of column C and have just one value in ONE cell (as shown on lines 13 & 14), so for instance:
When added together the values of rows 3,4 & 6 are 7.25 (based on a value of "W")When added together the values of rows 7 & 8 are 5.00 (based on a value of "F")
I have tried to show this as an example in the image below:
A
B
C
D
E
[Code]....
I'm using Excel 2010 on Win 7 Pro.
View 3 Replies
View Related
Aug 21, 2008
Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
View 9 Replies
View Related
Nov 5, 2013
I have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.
Example.xlsx‎
View 1 Replies
View Related
Jun 12, 2008
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View 5 Replies
View Related
Nov 26, 2008
I'm selling a product to clients. The first one is $20, and every subsequent one is $10. If they don't order any, I need the cell to read $0. I've been trying to get the IF function to work, and can't quite make it work correctly.
View 3 Replies
View Related
Jun 27, 2008
I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.
At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.
EXAMPLE:
TOP 20%
£1,330.00
£1,070.36
£997.50
£840.00
Employees - 4
SUM - £4,237.86
Median - £1,033.93............................
View 14 Replies
View Related
Dec 29, 2006
I have few x values
I have few y values corresponding to the x values.
How to calculate a x value from given y value based on the above data. I tried linear iterpolation, I am not getting good results.
View 14 Replies
View Related
Aug 4, 2007
I want to apply 4 conditions.
Core ValueCurrent Pricenew Price
a 400
b 600
c 300
d 300
If core value = a then new price should be current *2%, if core value=b, then new price should be current price times 2.2%, if core value = c than new price should be current price times 2.5% and if core value=d, than new price should be current price times 3%?
also would i be able to do a formula if core value is either a or b, than new price should be current price times 2% and if core value is = to c or d, than new price should be current price *3%?
View 6 Replies
View Related
Jan 26, 2008
I am working on a tax deduction formula for payroll. I need 3 formulas. The first formula needs to find two cells that are on the same row based off the employees status and gross income.
View 5 Replies
View Related
May 21, 2014
I have been using Excel for a while but i'm kinda stuck at one point where i have to calculate the sum of the numbers corresponding to the month of the data.
Column 1 has all the dates in different months - in this workbook i've included only jan, feb and mar. The second column has the counts corresponding to the dates.
Now if a user inputs the month (1,2,3) number in F2, then we need to compute the sum of all the numbers from second column corresponding to the month in column 1 and display in cell F4.
View 3 Replies
View Related
Jan 30, 2014
I have 2 tables:
IDTypeValue
1A3
2A3
3A4
4B4
5B3
6C2
7A4
8A3
And
TypeTotal
A
B
C
I need to "total" column to automatically display the sum of the values for the categories A, B and C. Is there a formula I can use for this?
View 2 Replies
View Related
Apr 21, 2009
I`m relatively inexperienced with Excel, searched for a few days and have not been able to figure out how to calcuate this value. I have multiple worksheets to work out financials. I have a separate worksheet with the pricing breakdown based on volume. 2 columns
0 - 99,999 0.05
100,000 - 499,999 0.04
500,000 - 999,999 0.03
1,000,000+ 0.03
On a separate worksheet I want to add I6+I7, depending on the value multiply it by the value in the second column. So if the total is 520,000 then multiply it by 0.03.
View 5 Replies
View Related
Jun 3, 2009
Need to calculate different percentiles based on different grades. how do I put an 'if' loop or anything else that will work for this.
GradeAmount
B23
B22
B35
B36
B48
B49
B510
B511
B615
B23
B37
B614
Need function for the table below
Percentile10%25%50%60%75%90%
B2
B3
B4
B5
View 12 Replies
View Related
Sep 8, 2009
I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:
KEYPerformance
2010,000
2120,000
2230,000
2340,000
I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:
KEYMin FeeFee 1Fee 2Fee 3Fee 4
201000.10.20.30.4
211000.10.20.30.4
221000.10.20.30.4
231000.10.20.30.4
For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:
Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999
So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.
How can this be combined into a lookup/range/low-high formula to extract the correct values????
View 13 Replies
View Related
Aug 10, 2005
example 1:
This years sales are $3700, a decrease of 11.6%. What would last years
sales be?
example 2:
This years sales are $4500, an increase of 151%. What would last years
sales be?
View 12 Replies
View Related
Aug 29, 2006
I want to be able to calculate a price based on a given quantity. But here is the trick.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 36 widgets, then the cost is 36 x $1.00.
If item A is ordered in quantities less than "12" than there is a 15% upcharge.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 4 widgets then the price is 4 x $1.15
But, if the customer orders 35. Then the first 24 are calculated at 24 x $1.00 and the remaining 11 are calculated at 11 x $1.15.
View 14 Replies
View Related
Jul 17, 2008
I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out.
Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?
View 11 Replies
View Related
Feb 12, 2009
I have some financial data that has been exported via sql from out financial system that lists transactions for each month in a column with 12 columns representing each month of the year in Sheet1. (My financial year runs July - June). The column headings are MONTHLY_TYR_BALANCE_01 thru to MONTHLY_TYR_BALANCE_12 where MONTHLY_TYR_BALANCE_01 = July, MONTHLY_TYR_BALANCE_02 = August etc.
To the right of this data I wanted to have one column that will list the MTD data each month ie in July this should reference to the column headed MONTHLY_TYR_BALANCE_01, and in August this should reference to the column headed MONTHLY_TYR_BALANCE_02
I can get the end user to type the month number into a field and reference back to this but I just can't think how to convert this to a column reference that I can then use.
View 2 Replies
View Related
Aug 14, 2012
I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.
Excel File as follows:
Planned Delivery Time Planned Order Day Planned Delivery Day
8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)
13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)
I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.
View 3 Replies
View Related
May 5, 2013
I have a number which consist of 12 digits such as 765432123345. The first two digits denotes the year. I need to create a formula in the next column to calculate the difference in years by comparing to the current year of 2013. In this scenario, the difference will be 37. Is there a way to do this?
View 5 Replies
View Related
Jan 21, 2009
Something has X views, 10,000 for this example. I want:
1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.
How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
View 9 Replies
View Related
Jan 17, 2009
We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase. My questions have two parts:
1. Is this something array formula can handle in one formula?
2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps. For example,
Month 1-15 Rent $20
16-27 20.50
28-39 21
40-51 21.5
52-63 22
64-75 23
......
......
......
232-243 30......................
View 5 Replies
View Related
