If you press Ctrl+F2 you can make cell formulas by clicking and typing and with some guidance. If you look up the VLOOKUP command, double click it and place the cursor in the Sort Order field, you can see information about that parameter. It says that if it's TRUE or NOT GIVEN, that means that the array must be sorted in ascending order. So not given is the same as sorted.
2009/5/29 Mark Knecht <[email protected]>: > 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) You could do that. You could also do something like this: =year&"-"&week Like, if the year is in A1 and the week in B1, you could enter the following into C1: =A1&"-"&B1 So, if A1=2004 and B1=20, C1 will be 2004-20. Well, that's one example. Maybe not what you are looking for, but maybe enough so you can find your way. Kind regards Johnny Rosenberg > > 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] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
