At 14:10 28/05/2009 -0700, Mark Knecht wrote:
I have a large table of weekly equity data coming out of another
program from which I need to collect a compacted table of the week
by week results. The table is currently about 10 years of data with
(typically) 5-10 lines/week. The input data to calc looks like the
following data, somewhat simplified for the sake of this email:
Year Week Equity
2004 2 374
2004 2 -52
2004 3 154
2004 4 774
2004 5 -426
2004 6 774
2004 6 618
2004 6 622
2004 6 1396
2004 7 644
2004 7 218
2004 9 354
2004 9 178
2004 10 454
2004 10 28
The resulting table I want to get in calc should look like this:
2004 2 -52
2004 3 154
2004 4 774
2004 5 -426
2004 6 1396
2004 7 218
2004 8 0
2004 9 178
2004 10 28
The final table consists of the last line of a given week number.
Note that if a week is missing I need to fill that in with a 0 value.
I wonder if someone knows of way to do this.
Suppose your original table is in columns A, B, and C - and that your
new table is in D, E, and F. Create the values in D and E (from row
2 down) manually. In F2, put something such as:
=VLOOKUP(E2;B2:C16;2)*(COUNTIF(B2:B16;E2)>0)
and fill it down the column.
The VLOOKUP() function searches the first column of the range B2:C16
for the value in E2 - the week number - and happily finds the last
where there are more than one. The "2" parameter means that you want
the value from the second column - here column C - returned. That
will do everything you need except for dealing with your missing
week. The COUNTIF() function counts how many entries there are for
the current week; the logical test finds if this is non-zero. If you
multiply a number by a logical value, as here, FALSE is interpreted
as zero and TRUE as one. The result of multiplying by this is not to
change the other values but to provide a zero value for the missing week.
If you need to incorporate the year into the test as well as the
month, you may need to combine these values in some way before using VLOOKUP().
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
