Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function or number depending on input
Hi, i've got a spreadsheet with a function i would like to duplicate: There are 2 cells, one with the Yield of a security (in this case a bond) and another cell with the price of the security (the same bond). Lets say A1 holds the price and cell A2 holds the yield. Now Excel functions Price() and Yield() use more or less the same arguments and let you calculate the Price of a bond, based on a certain yield OR let you calculate the yield of a bond, based on a certain price. So it's a bit like the chicken and the egg...you need a price to calculate yield or you need a yield to calculate a price. In this spreadsheet i'm having they allow you to type in the Price of a security in cell A1, which will give you a yield in cell a2. The nice thing however is the following: if you type a yield in cell a2 it will give you the price in cell a1. So based upon your input Excel looks at the cell where you have input the value and puts a function in the other cell....it calculates the result and puts the original function back into the cell where you have just typed a value, allowing you to do further calculations with that function....So even when you type a value in a cell, somehow excel still knows what functions should behind it once it has done it's calculations......Maybe i'm not explaining this very well but if someone has any idea how you can do this i would love to hear from you ! Thanks in advance!!!! -- Jan Jansens ------------------------------------------------------------------------ Jan Jansens's Profile: http://www.excelforum.com/member.php...o&userid=26982 View this thread: http://www.excelforum.com/showthread...hreadid=401914 |
#2
|
|||
|
|||
Dude please try to write this more clearly - I can't follow your question. Cut out the detail of what the formula is referring to and just call it a formula. Too much info at the moment sorry :) -- gearoi ------------------------------------------------------------------------ gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576 View this thread: http://www.excelforum.com/showthread...hreadid=401914 |
#3
|
|||
|
|||
sorry 'bout that.... here we go.... A bond has the following terms: February 15, 1991, settlement date November 15, 1999, maturity date 5.75 percent semiannual coupon 6.50 percent yield $100 redemption value Frequency is semiannual 30/360 basis Cell A1 = PRICE("2/15/91","11/15/99",0.0575,0.065,100,2,0) Cell A2 = YIELD("2/15/91","11/15/99",0.0575,95.04287,100,2,0) Normally 0.065 is the result of cell A2 and 95.04287 is the result of cell A1. Now in this spreadsheet i have they let you enter a value and then the value of cell A2 will change ( so i suppose it most look something like this in cell A2: =YIELD("2/15/91","11/15/99",0.0575,A1,100,2,0)). But if you type a value in cell A2 it will also change the value for cell A1 (so that should look like A1:=PRICE("2/15/91","11/15/99",0.0575,A2,100,2,0)) . So somehow excel puts the formula back into the cell A1 or A2 after you enter a value in it......without creating a circular reference........and while leaving the result of the calculations in the other cells..... I hope this clarifies it a bit........ -- Jan Jansens ------------------------------------------------------------------------ Jan Jansens's Profile: http://www.excelforum.com/member.php...o&userid=26982 View this thread: http://www.excelforum.com/showthread...hreadid=401914 |
#4
|
|||
|
|||
by the way...thanks for trying to help me out on this one.......i'm getting desperate to find a solution myself.... -- Jan Jansens ------------------------------------------------------------------------ Jan Jansens's Profile: http://www.excelforum.com/member.php...o&userid=26982 View this thread: http://www.excelforum.com/showthread...hreadid=401914 |
#5
|
|||
|
|||
On Mon, 5 Sep 2005 08:00:19 -0500, Jan Jansens
wrote: Hi, i've got a spreadsheet with a function i would like to duplicate: There are 2 cells, one with the Yield of a security (in this case a bond) and another cell with the price of the security (the same bond). Lets say A1 holds the price and cell A2 holds the yield. Now Excel functions Price() and Yield() use more or less the same arguments and let you calculate the Price of a bond, based on a certain yield OR let you calculate the yield of a bond, based on a certain price. So it's a bit like the chicken and the egg...you need a price to calculate yield or you need a yield to calculate a price. In this spreadsheet i'm having they allow you to type in the Price of a security in cell A1, which will give you a yield in cell a2. The nice thing however is the following: if you type a yield in cell a2 it will give you the price in cell a1. So based upon your input Excel looks at the cell where you have input the value and puts a function in the other cell....it calculates the result and puts the original function back into the cell where you have just typed a value, allowing you to do further calculations with that function....So even when you type a value in a cell, somehow excel still knows what functions should behind it once it has done it's calculations......Maybe i'm not explaining this very well but if someone has any idea how you can do this i would love to hear from you ! Thanks in advance!!!! Without seeing the s/s I can't be certain, but it sounds like there's probably some VBA code in operation here which is being driven by a Range Selection Change Event. i.e. when you enter a value in one cell, the Change event of that cell is fired and puts a formula in the other cell, and vice versa. You could check by looking in the VBA window ALT-F11, double clicking on the sheet name in the VBA Project Explorer pane, and then seeing what is in the Worksheet Selection Change pane over on the right. Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
|
|||
|
|||
hi ! thanks for your reply....you were indeed right...there is quite a bit of VBA code behind this spreadsheet. Most of it is password protected so i can't see how they've actually programmed it..... This probably isn't the right forum to ask whether someone has any idea how to program this in vba ? I know some VBA but this goes beyond my knowledge..... Tx all! -- Jan Jansens ------------------------------------------------------------------------ Jan Jansens's Profile: http://www.excelforum.com/member.php...o&userid=26982 View this thread: http://www.excelforum.com/showthread...hreadid=401914 |
#7
|
|||
|
|||
Jan Jansens Wrote: hi ! thanks for your reply....you were indeed right...there is quite a bit of VBA code behind this spreadsheet. Most of it is password protected so i can't see how they've actually programmed it..... This probably isn't the right forum to ask whether someone has any idea how to program this in vba ? I know some VBA but this goes beyond my knowledge..... Tx all! Just rewrite the s/s - much easier than trying to understand someone else's quote often :) -- gearoi ------------------------------------------------------------------------ gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576 View this thread: http://www.excelforum.com/showthread...hreadid=401914 |
#8
|
|||
|
|||
Quote:
It's not a question of "stealing" or "using" someone else's code....it's just that it's proven to be a very nice functionality for a lot of spreadsheets that i have...would just love to be able to create the code myself to duplicate it but unfortunately i'm not enough of a wizard in VBA to be able to do so ! ;) That's why i thought i asked to the people who DO know ! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round a number in nested function | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
clock | Excel Worksheet Functions | |||
Does Excel 2000 have a 'datedif' function to calculate the number. | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |