MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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...