wilmott.com

Excel has alot of quirks, the one I hate the most are the bugs with EVALUATE and user defined functions

I am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.

QuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.

I am using ADO.NET and OleDb to populate/configure Excel sheets. It's nice trick. And any language, almost can be used. Even Java.In this way the Excel data is 'untouched by human hand'.

QuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)

QuoteOriginally posted by: edouardQuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)The following works:in (say) C1: YEAR(A1)in C2 then dragging down: `=IF(C1="","",IF(YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))=C1,"",YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))))... but, this is ugly!

QuoteOriginally posted by: edouardQuoteOriginally posted by: edouardQuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)The following works:in (say) C1: YEAR(A1)in C2 then dragging down: `=IF(C1="","",IF(YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))=C1,"",YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))))... but, this is ugly!I agree that it is ugly, but it doesn't actually seem to work. It will produce a list of the initial consecutive years in which you have data, but blank out everything after the first year of missing data.

QuoteOriginally posted by: bearishQuoteOriginally posted by: edouardQuoteOriginally posted by: edouardQuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)The following works:in (say) C1: YEAR(A1)in C2 then dragging down: `=IF(C1="","",IF(YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))=C1,"",YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))))... but, this is ugly!I agree that it is ugly, but it doesn't actually seem to work. It will produce a list of the initial consecutive years in which you have data, but blank out everything after the first year of missing data.you might be right (the +1 stuff). that works with the series i have as there actually is no year missing.

QuoteOriginally posted by: outrunThis one is also a great new one I discovered:=SUM( Sheet1:Sheet10!A1:B1 )sums the values in the range A1:B1 across 10 sheets!Cool! How does Excel decide which sheets to include? Is it based on the order of the tabs? And if we have "Sheet1", "Sheet2", "Sheet3", "OutlierSheet", "Sheet4", ....., "Sheet10", then will the cells of "OutlierSheet" be included in the sum?

QuoteOriginally posted by: outrunI neede to count the number of ">" chars in a string. The approach is to use the SUBSTITUTE function and replace ">" with an empty string. Then you compare the length of the new string against the old string.= LEN(A1) - LEN( SUBSTITUTE( A1, ">", "") )this one is nice!

QuoteOriginally posted by: outrunQuoteOriginally posted by: Traden4AlphaQuoteOriginally posted by: outrunThis one is also a great new one I discovered:=SUM( Sheet1:Sheet10!A1:B1 )sums the values in the range A1:B1 across 10 sheets!Cool! How does Excel decide which sheets to include? Is it based on the order of the tabs? And if we have "Sheet1", "Sheet2", "Sheet3", "OutlierSheet", "Sheet4", ....., "Sheet10", then will the cells of "OutlierSheet" be included in the sum?Yes, so you can e.g. create two empty sheets "begin" and "end" and move all the sheets you want to sum inbetween them.[begin, end][begin, end)(begin,end)?

[begin, end]But I usually leave the sheets begin and end blank.