index, lookup, nested ifs, which is best for...?
Posted by Aron Gahagan on December 17, 2001 12:26 AM
I have been working at this for hours and have gotten nowhere. I have Dates (mm/yy) in Column A, Prices in Column B. Then in Column D I start a table; Months D2:D13 (by number), Years E1:E11. (Except I have hundreds of rows in the real list.)
Now, I'd like to automatically enter the prices in B1:B10 to their correct cells in the table. I tried using CSEs, etc., like =if(month(A1:A10)=D2,if(year(A1:A10)=E1,B1:B10)) with the appropriate absolute addresses (I think). I planned to then copy this one formula down and across and...voila. I've tried everything else I can think of; Index(), Value(), everything...can somebody help?
Posted by Aladin Akyurek on December 17, 2001 12:40 AM
Aron --
I don't understand why you'd want to do this.
Why not insert 2 columns after column A, use
=MONTH(A2) in B2 and
=YEAR(A2) in C2?
If you have some other reason for re-creating your data in D, E, and F, please post 10 rows of your data from columns A and B in the follow-up.
Aladin
========
Posted by Aron on December 17, 2001 8:10 AM
I've averaged prices by month to insert them in this chart so I can begin a seasonal fluctuation analysis and then a forecast (with and without seasonal adjustment. I know how do everything else except automatically insert the values in colum A and B in the table beginning in Column d.
Here is a sample of the data;
date Price
Jan-89 1.25
Feb-90 1.22
Mar-91 1.34
Apr-92 1.58
May-93 1.69
Jun-94 1.47
Jul-95 1.65
Aug-96 1.55
Sep-97 1.42
Then Months in D2:D13 and Years in E1:E10
thanks so much for even looking at this...
Posted by Aladin Akyurek on December 17, 2001 10:14 AM
Aron --
Given your data, I think it should be sufficient to enter
in D2: =MONTH(A2)
in E2: =YEAR(A2)
in F2: =B2
and to select D2:F2 then to copy down as far as needed.
Is this what you are looking for?
Aladin
=======
Posted by Aron on December 19, 2001 3:23 PM
Why doesn't this formula work (beyond the 1st column, even as a CSE)?
Thanks, but not really. Here is the formula I'm attempting to use, with Dates in D:D, Prices in E:E, Months in F2:F13, and years in G1:G13: =IF(MONTH($D$2:$D$636)=MONTH($F$2:$F$13),IF(YEAR($D$2:$D$636)=YEAR(G$1),$E$2:$E$636))
It only works in the first column and, strangely, only as a regular (not CSE) formula. I'm stumped.
Posted by Aladin Akyurek on December 19, 2001 10:51 PM
Re: Why doesn't this formula work (beyond the 1st column, even as a CSE)?
Aron --
I know what this formula can return. I'm however more interested in your goal: What do you intend to achieve? You can perhaps give the result that you expect.
Aladin
========= Aron -- Given your data, I think it should be sufficient to enter in D2: =MONTH(A2)
Posted by Aron on December 25, 2001 9:27 PM
Re: Why doesn't this formula work (beyond the 1st column, even as a CSE)?
I have a long list of data in a column with dates in column A and prices in column B. The dates in column A span 10 years. I need to rearrange the data into a matrix with months as rows, years as columns. Then I can analyze and project according to month/season, rather than the entire data set.
Sorry for the bizarre goal...
