Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

August 2009 - Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • Euler Problem 109

    Euler Problem 109 asks: In the game of darts a player throws three darts at a target board which is split into twenty equal sized sections numbered one to twenty. The score of a dart is determined by the number of the region that the dart lands in. A dart landing outside the red/green outer ring scores [...] Read More...
    Filed under: , ,

  • Cumulative Percent

    Recently I was asked how many customers made up 25% of my sales. I listed each customer and their sales for a specified period, then sorted descending on sales. The formula in C2 is copied down for as far as the data goes. The secret is in the referencing. =SUM($B$2:B2)/SUM($B$2:$B$51) In the denominator, both B2 and [...] Read More...
    Filed under:

  • NFL Slotting

    Peter King at SI says: The NFL has a slotting system that is ever-so-slightly malleable, where a player who gets drafted one spot lower than another player occasionally gets a smidgeon of a better deal. And sometimes a quarterback gets an above-market deal. But position players and non-quarterback skill players are slotted, and despite the efforts [...] Read More...
    Filed under:

  • Using VBScript to monitor Office events…or not

    I spent an unexpectedly long time trying to figure out how to monitor Office events using VBScript and this post shares my experience, largely disappointing. The below scenarios were tested with Vista Ultimate and Office 2007 as well as with Windows 7 Ultimate and Office 2010 Beta. This is about using VBScript through the Windows [...] Read More...
    Filed under: ,

  • Formatting Taskpane

    In order to perform my most common formatting operations, I'm going to try to use a userform with only those operations on it. These 16 buttons should cover about 95% of the cell formatting I do. I set the Ctrl+1 shortcut, which normally shows the Format Cells dialog, to show my userform instead. Now [...] Read More...
    Filed under:

  • Golf Charts — Another Take

    Inspired by Dick’s interest in charts, I took a look at how I would have presented the data. Some overall thoughts. I used Office 2010 beta for the charts shown below. There was not much, if any, difference between what I would have got with Excel 2007. I also stayed with the default [...] Read More...
    Filed under: ,

  • Golf Charts

    After week 8 of my golf league, I decided to have a chart-of-the-week. Now, for the first time ever, you can view all the charts o’ the week in one place with author commentary. Week 8 After scoring week 8, I noted that I was plummeting down the leaderboard while Miller seemed to be scoring well [...] Read More...
    Filed under:

  • Russian Peasant Spreadsheet

    If you consider spreadsheet formulas a programming language, and I do, then you could solve the Russian Peasant Multiplication challenge with a spreadsheet. D3: =SUM(D4:D35) A4: =B1 C4: =B2 D4: =IF(ISODD(A4),C4,"") A5: =IF(OR(ISTEXT(A4),A4=1),"",INT(A4/2)) B5: =IF(OR(ISTEXT(A4),A4=1),"","x") C5: =IF(OR(ISTEXT(A4),A4=1),"",C4*2) D5: =IF(OR(ISTEXT(A4),A4=1),"",IF(ISODD(A5),C5,"")) Then filled down. Read More...
    Filed under:

  • Russian Peasant Multiplication

    The Daily WTF posted a challenge to code the Russian Peasant Multiplication. Here's mine: Function RussianPeasant(lFirst As Long, lSecond As Long) As Double         Dim lDiv As Long     Dim lMult As Long     Dim lMod As Long         lDiv = lFirst     lMult = lSecond         Do Until [...] Read More...
    Filed under:

  • Make Office Better

    JP posted about Make Office Better, so I checked it out. First, I have to say that I love the design of the site. I didn’t know anyone at MS knew how to design clutter-free websites anymore. Maybe that could add a few more suggestions per page and get rid of the [...] Read More...
    Filed under:

  • Quick Find Regular Expressions

    I thought I’d add regular expressions to my Quick Find utility. I’ve used regular expressions about a dozen times in my life, so why not clutter up a simple tool with it. Now if I start my search string with rx: it will evaluate the rest as a regular expression. My problem is when [...] Read More...
    Filed under:

  • Euler Problem 205

    Euler Problem 205 asks: Peter has nine four-sided (pyramidal) dice, each with faces numbered 1, 2, 3, 4. Colin has six six-sided (cubic) dice, each with faces numbered 1, 2, 3, 4, 5, 6. Peter and Colin roll their dice and compare totals: the highest total wins. The result is a draw if the totals are [...] Read More...
    Filed under:

  • Quick Move

    In my continuing series of tools to save myself literally seconds every day, I present Quick Move. See also QuickTTC, QuickPivot, and QuickFind. Even though I'm a keyboard guy, I tend to move or copy sheets within a workbook using the mouse. I generally only use Edit - Move or Copy Sheets when I [...] Read More...
    Filed under:

  • Moving Sheets

    I'm working on a utility that requires that I move a worksheet. I thought it would be pretty easy, but now I'm rethinking it. Maybe it is easy and I'm just dense. I'll walk you through my process and you can tell me what you think. Step 1 Activesheet.Move What could be simpler? Oh, [...] Read More...
    Filed under:

  • The Big Bang Servo Diet

    From Philip Greenspun You draw a line from the current weight/date to the desired weight/date. Every morning you weigh yourself and plot the result. If the point is below the line, you eat whatever you want all day. If the point is above the line, you eat nothing but broccoli or some other low-calorie food. The thing [...] Read More...
    Filed under:
1 2 Next >
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.