Have 5 columns (A1-E1) with different bond data: (respectively)
Coupon, Maturity, Settlement, Price, Yield (asset swap is F1)
Depending on the type of swap I am performing i want the ASWAP (user-created) function to reference different values within the row.
For example: Swap 1 = settlement, maturity, coupon, price, curve
(curve is a user input single number 1-3)
Swap 2 = settlement, maturity, yield, price, curve (yield in place of coup)
Swap 3 = settlement, maturity, yield, 100, curve (100 for par redemption)
etc. I won't type out all the iterations, not necessary.
The way that I want the ASWAP to recognize which Swap# to use is by inputting a number into a cell beside the Aswap column that I can input 1, 2, 3, etc., and then recalculate to get the desired outcome. Want to keep this a static reference as well as the price at par (each owning their own single cell, not a fill down the columns because there are too many).
So assuming G1 is the Swap# input, and H1 is 100...
How do i write a formula that is capable of reading the input in G1 and outputting the proper swap calc.
My gut feeling was along the lines of nested IF's with the corresponding formula... but i won't mess around in futility on here.
Let me know if this is too confusing without me putting up an example.
I'm trying to calculate the Depreciation of the fixed asset for some items. I've tried the formulas that came with Excel but i don't know its not working or not give the correct value so I attached a file as an example what I'm trying to have is
1- straight line method along the asset life
2- salvage must be ( 1 )
3- if the purchase date is equal to or before the middle of the month (14-15/02/2004) so the start of depreciation have to start from the beginning of the current month ( Feb) but if the date excess the day 15 ( 16/02/2004) the middle of the month , the the depreciation must start from the next month.
I am looking to reassess the useful life of existing fixed assets by one/two years.
For example:-
Original cost $1,727.00 Start Date 30/12/2008 Current End of life 29/12/2013 New End of Life date 29/12/2014 5 year - 20% NBV as at 30/06/2013 $174.12
How can I calculate the new rate, as I can not change the prior financial years depreciation. I am looking for a calculation, to test the assumptions, and see the overall change. This is just one example.
I am working on a depreciation schedule in which I want the monthly depreciation of an asset to automatically calculate and, if the asset is fully depreciation, caclulate a zero or the balance to be depreciation (if less than the monthly depreciation). Please see below example. As you can see my asset is fully depreciated at the end of February but because there remains a $0.01, the formula is calculating another month in March and then reversing it in April (less the $0.01). Here's the formula I'm using. What am I doing wrong?
Column H is March, Column C is my monthly depreciation, and column E is my beginning book value:
I have the following formula which returns the number of asset breaks for each head of dept in sheet3 based on their teams in Sheet1 and then does a countif in >30_DAYS_ASSET in sheet2. In sheet 1 is a mapping table with range names "Teams2", "Teams" & "Managers". I now need the formula to also do a count on the unique numbers in sheet2 Column B, if there are duplicate numbers then that will be 1 asset break, so for example for for Joe plummer we can see there are duplicate case numbers so the formula in total should return 2.
in fact the sheet could contain hundreds if not thousands of rows.
Look at column Player 1, the last 3 characters are AoS.
I want to find any rows where Player 1 has those three characters ( AoS ) and swap them with the contents of the Player 2.
BUT if that swap happens then the following swaps must occur as well:
Contents of Column "Side 1" swaps with Contents of Colum "Side 2" Contents of Column "Player 1 Pts" swaps with Contents of Column "Player 2 Pts"
Ultimately I want this extended so that is the letters AoS appear in any of the following columns ( Player 4, Player 6, Player 8, Player 10) the following swaps happens:
Contents of Column "Player 3" swaps with Contents of Column "Player 4" Contents of Column "Side 3" swaps with Contents of Column "Side 4" Contents of Column "Player 3 Pts" swaps with Contents of Column "Player 4 Pts"
Contents of Column "Player 5" swaps with Contents of Column "Player 6" Contents of Column "Side 5" swaps with Contents of Column "Side 6" Contents of Column "Player 5 Pts" swaps with Contents of Column "Player 6 Pts"
I want to swap values of two cells. can anyone help me on that? Eg. Cell A1 contains the value 20 & cell A2 contains the value 100. I want 100 on A1 & 20 on A2.
I have column AD where there are something like 2,500+ formulas. I would like to change the columns used in the formulas around. By that I mean the first formula is...
Code: =SUM(AA6-X6) ...and I would like it to be...
Code: =SUM(X6-AA6)
Now the thing is, the formulas are not continuous, there are breaks where there is a formula to sum say the previous dozen or so for example, then it skips a couple of rows and I have more formulas and then the sum say for the previous dozen for example and so on.
I just want those cells changed with the particular formulas as above.
I have let say column A and I have the date and name John 1913 all the way down to John 1940.
But what I need is a way to turn it around so A1 starts with John 1940 down to 1913.? If I drag down it counts up but I want it to count down if that makes sense.?
I would like to insert a form control (up and down arrows) into a specfied range of cells in a column - ideally the first column. When the form control up or down arrow of the assigned row is clicked it moves that row up or down essentially swapping positions with the row directly above or below. I've seen this functionality in custom excel applications but my searches for code on this topic have been fruitless.
Right now I have 2 ranges say A and B. Range A is in a worksheet on row 7 and range B is on the same worksheet row 8. What I want to do is make it so that range B gets pasted in for range A and vice versa so that range A in now in row 8 and range B is in row 7.
I am trying to chart a range of dates, for example. I have some boats that are on contract between dates a and b. I would liketo plot several boats with their contract date range. I have tried using a bar chart, but all the lines start at 0. I got it working using an Excel 2007 Stock chart, however, it is in the wrong orientation. I would like to Rotate the Chart 90 degrees, swap the axis, or find a better method all together. I cannot post the spreadsheet as its confidential.
My pivot table shows Towns as the Row headings and Month as the column headings. When I do a Chart, I get Towns on the X axis and Months as Y. I want the chart to show all my May data in a stacked column, then all my June data in another stacked column, etc. If I pick "Select Source Data" the options are greyed out. What's a mother to do?
I have an interactive chart displaying stacked area charts for 2006 and 2007 I currently have 2006 in front of 2007, but as the user chooses 1 of 20 units, it will look better to move 2007 in front of 2006
I am using the code below to "swap" the selected cell values in a column with the ones to the right of the selection. It works fine on unfiltered sheet, but if I apply an Autofilter, it copies both visible and hidden cells, and I only want to swap visible cells. What to modify?
If the user selects French (radio button selection), I want to swap out the existing English text for French and vice versa. One large text box with several paragraphs. Can Excel do this?
I am trying to take two random Mouse click selections and swap a set of ranges associated with the cells that are selected. For example if the user clicks on A1 it will resize(3,22) and store the selection as a variable to be swapped with another selection. Here is what I have so far but I keep getting a object required error on rngEmp1.
VB: Sub SwapGroup() If Selection.Cells.Count < 8 Then MsgBox "Please Select two Groups to swap. Press and hold 'Ctrl' in between your selections", , "Swap Groups"
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
VB: Function f1(Matrix As Range) 'Does something and returns f1 = a double End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB: Function f2(dD As Double) Dim MatrixRed() As Double Redim MatrixRed(1 To dD, 1 To 10) For i = 1 To dD For j = 1 To 10 MatrixRed(i, j) = i * j Next Next f2 = f1(MatrixRed) End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in Match: Lookup value = $E3 Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1 match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4 Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452 match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
