combination formula
Posted by Michael Parkinson on June 14, 2001 8:27 AM
I currently have a basic spreadsheet created which
shows dates in the first column and numbers(returns) in the following colums. I would like to set up a cover page on a different part of the sheet which will look up the cell which corresponds to the referenced date, then find the cell which has the repective return. This would be done by "V" or "H" LOOKUP, however,I would also like to create a formula which then takes the data in the "VLOOKUP" reference (return) cell and produce the "PRODUCT" of a range of cells downward creating the 1yr(12), 3yr(36), or 5yr(60) calculating the geometric link of these cells.
Posted by Aladin Akyurek on June 14, 2001 8:32 AM
Can you provide a snippet of your data? And, what is a geometric link?
Aladin
Posted by Mark W. on June 14, 2001 8:33 AM
You lost me (perhaps, not enough coffee yet). Please
provide some sample data and the desired result
based on the sample data. Thanks.
Posted by Michael Parkinson on June 14, 2001 8:44 AM
A B C D
A B C
1 1/31/01 5.5 2.1 3.4
2 2/28/01 6.6 1.1 2.2
I need on a cover sheet to look up a value
associated with a date. Ex. Vlookup(1/31/01,A1:D3,2,False) would give me the 5.5 cell.Now I would like to take this cell and apply a product function(geometric link) with the cell and cooresponding cell below to produce a return.
Hope this helps
Posted by Michael Parkinson on June 14, 2001 8:51 AM
Hope this helps...
1st column lists dates 1/31 2/28 ect..
next column has a header(composite name) and an appropriate number(return) which corresponds with the date and composite. I am trying to create a sheet which looks up the number via date. Then,
take this number and multiply with the corresponding numbers in the column in increments of 12, 36, 60 which will provide a 1yr 3r 5yr return.
Posted by Aladin Akyurek on June 14, 2001 9:01 AM
Lets see.
In E1 enter: =VLOOKUP(A1,A1:D2,2,0)
In E2 enter: =INDIRECT(ADDRESS(MATCH(A1,A1:A2,0)+1,2))
Michael -- I consider your basic quetion to be How to get the "corresponding value". I guess it's 6.6 in your example. The formula in E2 will deliver that.
Caveats. Your data must be sorted on dates.
Your product function will be apperantly applied to the values of E1 and E2.
Am I on the right track?
Aladin
Posted by Aladin Akyurek on June 14, 2001 9:10 AM
Oops..
Replace the reference to the lookup value (i.e. A1 in the suggested formulas) by the ref of a cell where you put the value to look up. Sorry about that if it was confusing.
Posted by Michael Parkinson on June 14, 2001 9:28 AM
Re: Oops..
I kind of see where you are going with this
but instead of a value can I lookup an address
a cell asscosiated with the value? Vlookup(1/31), 2(address),0)
Posted by Michael Parkinson on June 14, 2001 9:36 AM
Kind of... I want to use the corresponding date to lookup cell address of the corresponding return, then multiply the number in the specific cell with the cooresponding number ranges 12spaces down, 36spaces down, 60spaces down.
Posted by Aladin Akyurek on June 14, 2001 11:36 AM
Re: Oops..
Michael
Addresses of which cell or cells (instead of values) would you like to have? I'm afraid I'm a bit confused by your "Vlookup(1/31), 2(address),0)".
Aladin
Posted by IML on June 14, 2001 11:43 AM
Crazy Guess
I'm probably way off base, but from the information below I'm guessing this may work
=PRODUCT(IF(((sheet2!A1:A36<=A1)*(sheet2!B1:B36)*(sheet2!A1:A36>EDATE(A1,-12))),((sheet2!A1:A36<=A1)*(sheet2!B1:B36)*(sheet2!A1:A36>EDATE(A1,-12)))))
This assumes you type in the value on one sheet, and your data is on sheet 2 in A1:A36
This is the product of the last 12 months. You can alter this by changing -12 in the edate formula.
Good luck.
Posted by Michael Parkinson on June 14, 2001 12:29 PM
Re: Oops..
Aladin,
I appreciate your patience. Let's start again from the beginning as I think I'm confusing myself. I'm not an Excel wizard but am learning.
My objective is to create a two cells. The first cell contains a date. The second cell displays a number through calculation of a particular range of cells associated with that date referenced in the first cell. When I change the date of the first cell I want excel to go to the range, find find this date, then find the "related value" (over "x" spaces in the range). After this is completed I want excel to take this related value and apply a multiplitacion function with the values(rows)underneath the related value, either (12,36,0r 60 rows)to come up with my answer. Can you help???
Posted by Aladin Akyurek on June 14, 2001 1:35 PM
A guess
Consider the following data occupying the range A1:C10.
{35460,5.5,2.1;35488,6.6,1.1;35519,4.6,3;35549,7.8,4;35580,3.4,6;35610,1.2,2;35641,6.8,10;35672,9,4;35702,8,3;35733,8.2,2}
Note. Numbers like 35540 are "dates" (internal serial numbers). No need to get confused by them.
In E1 enter: 31-Jan-2001 [ the date of interest ]
In F1 enter: 2 [ the column of the values of interest ]
In G1 enter: 5 [ the number of rows of values you want, like yours 12, 36, 60 ]
In E3 enter: =ADDRESS(MATCH(E1,A1:A10,0),F1) [ this returns the address of the value in column B, which is associated with the date in E1 ]
The formula in E3 returns for this example the following address/range:
$B$1
In E4 enter: =ADDRESS(MATCH(E1,A1:A10,0)+1,F1)&":"&ADDRESS(MATCH(E1,A1:A10,0)+5,F1) [ this returns the range of 5 values in B below the value whose address is computed in E3 ]
The formula in E4 returns for this example the following address/range:
$B$2:$B$6
Now you can use the addresses computed in E3 and/or E4 in formulas to carry out some computation.
For example:
=AVERAGE(INDIRECT(E4))
If you change the date in E1 and/or the column number in F1, the ranges which are computed will also change.
Aladin
==============
