Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value or formula in cell 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 this is not too clear so here's an example of what i want to do... 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..... Really appreciate any suggestions you may have as i'm getting desperate on this one....... -- Jan Jansens ------------------------------------------------------------------------ Jan Jansens's Profile: http://www.excelforum.com/member.php...o&userid=26982 View this thread: http://www.excelforum.com/showthread...hreadid=406186 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value or formula in cell depending on input...
I'm not sure I completely understand, but.....sounds to me that:
1) the user can enter a value into either A1 or A2, but not both 2) the other is calculated based on the data entered (eg, data in A1, formula in A2 or vice versa) 3) something else is calculated in A3 based on both A1 and A2 Sounds like a something that could be accomplished with the change event. John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value or formula in cell depending on input...
no really John.... 1) User can enter a value both in A1 or A2 ...if he enters a value in A1 then value in A2 changes, if he enters a value in cell A2 then value in cell A1 changes.... 2) so both A1 and A2 contain a formula but can also take values....(or so it looks) Thanks for taking a look at this..... -- Jan Jansens ------------------------------------------------------------------------ Jan Jansens's Profile: http://www.excelforum.com/member.php...o&userid=26982 View this thread: http://www.excelforum.com/showthread...hreadid=406186 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value or formula in cell depending on input...
no really=not really : -- Jan Jansen ----------------------------------------------------------------------- Jan Jansens's Profile: http://www.excelforum.com/member.php...fo&userid=2698 View this thread: http://www.excelforum.com/showthread.php?threadid=40618 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value or formula in cell depending on input...
They can't both contain a formula that refers to the other cell without
getting a circular reference error unless you have unchecked iterations in the options=Calculate tab. If you have, then you can do it with the change event as suggested by John. http://www.cpearson.com/excel/events.htm Chip Pearson's page on events. -- Regards, Tom Ogilvy "Jan Jansens" wrote in message ... no really John.... 1) User can enter a value both in A1 or A2 ...if he enters a value in A1 then value in A2 changes, if he enters a value in cell A2 then value in cell A1 changes.... 2) so both A1 and A2 contain a formula but can also take values....(or so it looks) Thanks for taking a look at this..... -- Jan Jansens ------------------------------------------------------------------------ Jan Jansens's Profile: http://www.excelforum.com/member.php...o&userid=26982 View this thread: http://www.excelforum.com/showthread...hreadid=406186 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a formula to input an entire row from another workbook depending on a certai | Excel Discussion (Misc queries) | |||
updating cell range depending on input value | Excel Worksheet Functions | |||
Increasing number depending on input | Excel Worksheet Functions | |||
Fill a seperate cell with info depending on the input in another c | Excel Discussion (Misc queries) | |||
Background Colour Depending On Input | Excel Discussion (Misc queries) |