On Thu, May 28, 2009 at 3:56 PM, Mark Knecht <[email protected]> wrote: > On Thu, May 28, 2009 at 3:15 PM, Brian Barker <[email protected]> > wrote: >> 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 >> > > Brian Barker - saving the world one Calc user at a time. > > It does help. I'll need to spend some time putting it together but the > idea makes sense. > > Thanks, > Mark >
Hey Brian, Turns out I am still a bit confused about the VLOOKUP function. It works, exactly like you suggest it should in this application, but reading the help file I have the impression that since we didn't set the SortOrder variable, telling calc that the table is sorted, that VLOOKUP would return the first value that meets the criteria of an exact match. However you say that it returns the last, which indeed it does seem to do. It seems to say that if the exact value is not found it will return the last value smaller than the search, but if the exact value is found, which it should be, then it still returns the last value? Where am I going wrong in my reading of the help file, or is it just not really there? Assume for a moment I wanted to find the first occurrence? Can VLOOKUP do that? (Again, assume a sort table as all mine will be.) Lastly, I do need to include the year. In truth, the table isn't sorted unless I do as the 20th week (number 20 in the table) occurs every year. When you said I might need 'combine the values' before the VLOOKUP, what might be an efficient way to do that? I thought of something like: =year+((week #)/100) which I think gives me a unique value for every row. Make sense or is there a better/more standard way to do this? Thanks, Mark --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
