Search results

Your link doesn't work. Are the columns in your table formatted for the data they contain? i.e. a date format applied to the entire column with dates? Asa

You can also use Data - Outline - Subtotal to group your data by truck and create an outline that has one truck heading similar to your example output. It will have a total line for each truck that can just give a count of records. For more info, check the Excel 2010 Help on Filtering and...

Hello Dannis, I'm pretty sure I saw a tip in the comments to one of Chandoo's articles the other day that said you can use a line break as a field separator for data import. To do so, where you specify your delimiters, choose "other" and pres ALT-010 (010 on the numpad) to specify a linefeed...

Thanks for sharing that info! I've just skimmed that paper thus far.. but I am very interested in statistical analysis in general, so you are certainly not boring me. The formula Benford put forth is fairly simple to duplicate in Excel and we could even put together another array formula, I...

I said in my last comment you could use my example as a data validation rule... as a validation rule, you would actually want to check that there was exactly one record, not that there were duplicates! For that, change the >1 at the end to =1 Asa

Great! I actually was under the impression that you could simply use ActiveCell.Interior.Color=vbButtonFace Obviously not! Thanks too for the lesson and the code. Your code comments say the functions need to be declared as private, but not necessarily. If Private, they can only be...

For the countif example, you can use the same logic you would use for multiple fields in a conditional formatting rule. Instead of concatenating fields, it is both less demanding on the cpu (no concatenation to do) and more precise (if any of your fields had a "|" in them the concatenation...

Figure out the problem in my GCD formula. GCD can accept an array argument, so is resolving the array before MAX. I wanted the array to survive the GCD call and have the set of GCDs evaluated by max, rather than one GCD for a set of numbers...

I don't know the answer, but I think maybe you can't interact with the spreadsheet, particularly in slideshow view. I am "mostly guessing" though :) You could be asking in the wrong forum. These looked interesting though...

Hey auto, I have nothing against using datedif... Hi kmakifl, Column B is Hire Date, C, Annual, D, Promotion Date, E, 45 (Day Eval Due Date) F, Status (days until due, 45 day eval) G, 90 (Day Eval Due Date) H, Status (for 90 days) I, 180 J, Status (180) K, 365 L, Status (365)...

Hi smc, Some errors: Master Dept. Rept. / cells F74:K74 each have circular references. Instead of =SUM($F$66:$F$74), etc. in those cells (which include themselves in the sum), put: =SUM(F$66:F$73) in cell F74 (note last cell is now F$73 and the removal of some of the dollar signs) and copy...

Since the default color of standard controls are based on system colors, they can vary according to version of windows and the user's theme/color settings in Windows. Windows has special color values that instead of being true RGB values are linked to the color theme properties. VBA has...

Easiest would be to change your data so the numbers are padded with spaces to the same length (a space before the 3). That should resolve the sorting for the first level of hierarchy. Still an issue with roman numeral sorting, though. Could you keep the the hierarchy levels in three separate...

Any file sharing site is fine. Some suggestions are here: http://chandoo.org/forums/topic/posting-a-sample-workbook I also like box.net. Asa

Mike, I would have expected your formula to work without array-entering it. It seems like SUMPRODUCT is not able to treat the range 1:6's in your formula as an array, must have hit on some peculiarity of sumproduct formula evaluation. Since you are calculating no products for your formula...

Hi smc #1, The official post on the matter: http://chandoo.org/forums/topic/posting-a-sample-workbook Any file sharing site will do, just make sure it is publically shared and you have the right link for the file. Another good site not mentioned in that post is box.net.

Glad it's working for you! I'm on my way to bed, but a hint with the ROW() thing.. The ROW() function retrieves the row number of a cell, or creates an array of row numbers if given a range of cells. ROW($1:$6) refers to the range of all the cells in rows 1 to 6. It will create the array...

The first of the mediafire links worked fine, didn't try the second. OK-- * in the first tab (Degrees of Roundness) the formjula in column B was fine except that the ROW portion was changed to ROW($3:$8) -- it must be ROW($1:$6) for up to 6 zeroes.. you can change the 6, but the first number...

Sorry; that link is informative about applying locale, calendar, and numeral shape settings to number formats, but NOT to change the decimal separator. My searches only turned up the suggestion to use a VBA function to change Excel's overall setting (the setting pointed to by magbo). But that...

OK, well the link above is great for showing dates, times and the like according to locale, but nothing on changing the decimal separator. My Google search seems to indicate the option does not exist as a cell format. You can change the computer's regional setting, or change the Excel...

http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx

Hi rabc, is the WEEKNUM() function what you're looking for? WEEKNUM gives you the week number of the year If you want to know the number of complete weeks between two dates, you could use: INT(((end date)-(start date))/7) Then there is the now largely undocumented DATEDIF function. It...

Hi Persol, I just saw your prior update about trouble... I can't open the file on Sky Drive, it says "This item might have been deleted, expired, or you might not have permission to access it. Contact the owner of this item for more information." Make sure it is publicly accessible. Thanks Asa

You're very gracious, Persol. I have another idea.. unfortunately -- it doesn't work!!! There's no reason for it not to, though, other than the fact that I've obviously madfe a basic error in my formula. Someone handy with array formulas (Mike?) will hopefully be able to fix this...

If you provide what data is causing the formula to give a VALUE error, I can tell you what the problem is. I simply fixed the main error in your formula and moved the formatting out of the formula into the cell format, but I'm not clear on what data is in what cells. Actually, if you post a...