Tag: excel

  • Independent date x-axis in Excel charts

    The Ask Metafilter question “Basic line graph” spurred me to make this unpretty graph:

    Here are the input data:

    You need to make an X-Y (Scatter) plot. All others don’t have fully independent X-axes. Make your chart by selecting the first two data columns, then Insert Chart. Once the chart is in, right click/Control-click (OS X) to get this context menu:

    which should show

    You can add more data series by hitting Add. Here are the specs for the next two series:

    Finally, here’s the example spreadsheet: MultipleDateLinesChart.

  • Calculating the second last Friday of the month

    My boss, bless ‘im (no really, do; he’s a sound bloke, great guy to work for, and is just getting through some serious health problems), needs a monthly status report on the second last Friday of every month. I live by my calendar applications reminding me to do things, so I thought it’d be no problem getting Outlook to set up a reminder.

    No dice; it will only set up appointments on the 1st, 2nd, 3rd etc., starting from the beginning of the month. I did a web search, and really thought I’d found a solution for iCal. It was not to be; this was for a Unix program called ICal; dratted case-insensitive search. Curiously, it appears that the ics spec might support a second-from-last syntax, but Outlook and iCal (and Google Calendar) can’t create them. Phooey.

    So I tried excel; and really thought I’d found the basis of an answer: Last Friday of the month. And indeed, most of their assumptions are right; the code

    DATE(year,month+1,1)-WEEKDAY(DATE(year,month+1,1),1)

    really does give you the date of the last Saturday in the month. But you can’t assume that the day before the last Saturday is the last Friday – it is the second last, if the month ends on a Friday (April 2010 is a test case).

    So I tried the Swiss Army chainsaw of brute-force date calculation: Perl with Date::Calc. What I do here is create an array of every Friday in the month, then print the second last member; never known to fail:

    #!/usr/bin/perl -w
    # second_last_friday.pl - show date of 2nd last friday
    use strict;
    use Date::Calc qw(Today
      Nth_Weekday_of_Month_Year
      Add_Delta_YMD);
    my ( $new_year, $new_month ) = 0;
    
    my ( $year, $month, $day ) = Today;
    foreach ( 1 .. 24 ) {
        my @fridays = ();   # for every friday in this month
        foreach my $week ( 1 .. 5 ) {
            if (
                ( $new_year, $new_month, $day ) =
                Nth_Weekday_of_Month_Year(
                    $year, $month, 5, $week
                )
              )
            {               # day of week 5 is Friday
                push @fridays, $day;
            }
            else {
                last;       # not a valid Friday
            }
        }
        printf( "%4d/%02d/%02d\n",
            $year, $month, $fridays[-2] );
        ( $year, $month, $day ) =
          Add_Delta_YMD( $year, $month, 1, 0, 1, 0 )
          ;                 # month++
    }
    exit;
    

    and this gives

    
    2009/11/20
    2009/12/18
    2010/01/22
    2010/02/19
    2010/03/19
    2010/04/23
    2010/05/21
    
    ...
    
    

    See, notice the tricksy 23 April 2010, which – considering thirty days hath April et al – ends on a Friday and threw that simple Excel calculation off.

    I’m disappointed that all these new applications like Outlook and iCal don’t seem to handle dates as elegantly as the old unix programs I used to use. pcal, in particular, could generate incredibly complex date formulae. I must dig around to solve this problem – and for now, actually have to remember to write that report on the second last Friday of this month …

  • how irritating

    OpenOffice doesn’t import the EOMONTH() function from Excel spreadsheets, but knows what it is when you type it manually. C’mon, people, get hep!

  • Display or hide zero values – Excel – Microsoft Office Online

    Display or hide zero values – Excel – Microsoft Office Online
    You may have a personal preference to display zero values in a cell, or you may be using a spreadsheet that adheres to a set of format standards that requires you to hide zero values. There are several ways to display or hide zero values.

  • excel: alpha code to numbers

    I use an annoying program that labels its output A..Z, AA..AZ, BA … rather than numerically from 1. This is annoying, as a spreadsheet won’t sort it correctly (it does A, AA, AB …). The following code will convert this code to the right numbers, assuming your alpha code is in cell B3:

    =IF(LEN(B3)=1,CODE(B3)-64,26*(CODE(B3)-64)+CODE(RIGHT(B3))-64)

    This will only work for codes of two characters or less, and is case sensitive.

  • excel: pasting only visible cells

    Ever tried to paste a range of cells in Excel that included hidden cells, but you didn’t want the hidden stuff? This works: Now Who Told Excel To Paste That Data?

  • “paste special: values” is special

    This saved me a bunch of time yesterday when I was pasting many sets of values into a spreadsheet: Daily Dose of Excel » Blog Archive » Mouse shortcuts.

  • house concert!

    We went to a house concert last night to hear Chris Coole & Erynn Marshall play some Kentucky duets. Erynn’s back from BC to record with Chris; today’s a long day in the studio.

    Great music, nice venue, excellent evening. Maybe we’ll eventually get enough money to buy Chris a new banjo head; his current one looks stricken with some dread skin disease …

  • pastry abuse

    Excel can (but probably shouldn’t) be used to make charts like this:

    hyperdonut

  • the late bp helium rocked the boat

    the late bp helium played The Boat last night, and as a wise man once said, the crowd roared like a lion. I love that psych guitar sound that he has, and the band is really tight together. I got a chance to chat with Bryan — whom I know from fegmaniax — and he’s a fine bloke. He has mad guitar skills, to boot; reminds me of The Soft Boys, with serious effects pedals.
    Jack & Ginger were excellent too, as were Henri Fabergé & The Adorables.

  • convert an angle to a bearing in excel

    I can’t believe I had difficulty with this one for so long:

     =MOD(450-angle,360)
    This assumes you’re measuring the angle in the usual Cartesian way; anticlockwise from the x-axis.

  • Rum Do At WindShare

    WindShare‘s having a special general meeting tonight to discuss the following resolution:

    Moved that the Board of WindShare recommends to the WindShare I membership at their general meeting of June 7, 2006, the merger of WindShare I and WindShare II for the purpose of entering into the activities necessary for the development of the proposed Lakewind Proposal.

    This is quite an important step, and since I’m still in Pittsburgh, I’d hoped to vote by proxy. I was informed by the WindShare administrator that this wasn’t possible; the Cooperative Corporations Act does not allow proxy voting.
    I’m annoyed by this, as it looks like WindShare is going to merge its capital with a 10MW project being built on a site with a 6.5 m/s mean wind speed. I wouldn’t develop a project on a site with this low a wind speed, so I asked the following of the board:

    Can you clarify, please, that the vote can only be carried if a majority of WindShare members are present at the meeting? It would be grossly unfair if an important vote like this one was carried by a minority.

    I would also like to have questions brought to the board, and if possible, the meeting itself. The LakeWind information package states that Bervie has “an average wind speed of 6.5m/s … making this an excellent site for Ontario”. I would not consider a site having this wind resource to be excellent, and it would certainly not be one that would attract a commercial developer. So my questions are:

    • Is it in the membership’s best interests to develop a relatively low wind site? WindShare made their political point with the ExPlace turbine, and now we must show that community wind is economically viable.
    • Would either of the potential sites be forced to curtail output when/if the extra Bruce units come online? While LakeWind would be connecting to local distribution, any generation in that area might be subject to queueing limitations.

    So far, I’ve heard nothing, which makes me uneasy.

  • my home and adopted land

    I became a Canadian citizen just after 10:00 today. I was the only Scot out of 107 new Canadians.

    My planning’s excellent; my UK passport expires today…

  • ex-GG at the movies

    We saw Brokeback Mountain at the Cumberland this evening, and who should sit next to us but former governor-general Adrienne Clarkson and her posse.

    I think she wanted our seats, as Catherine had got there early, and nabbed excellent ones; centre-row, 1/3 back.

  • EcoBunk Unplugged: the 15th Annual EcoBunk Awards

    ecobunk unplugged 2005
    TEA sez:

    EcoBunk Unplugged
    the 15th Annual EcoBunk Awards

    For advertising excellence in confusing the public & compromising the environment.

    Our annual fundraiser and comedy show pokes fun at the most outrageous corporate green advertising of 2005. Sometimes we even point the finger at ourselves. We present nominated ads under nine different categories and reveal the winner. The laughter lasts for two full hours.

    Of course, we don’t actually send awards to the winning companies.

    Come celebrate with us! Ecobunk is a popular and favourite event among the environmentally-minded in Toronto, Waterloo and points beyond.

    Thursday, December 8th, 2005
    Plaza Flamingo
    423 College Street
    Show starts at 8:00pm
    Doors & Cash Bar opens at 6:30pm
    Tickets: $20
    To reserve your seat(s) call TEA 416-596-0660

    *** Note we are asking for prepayment this year and can accept credit cards or cheques. ***

    Don’t miss the event this year!

    I’ll be there. Will you?

  • failing to excel

    The MS Excel spreadsheet function for square root is called SQRT. The MS Excel VBA macro function for square root is called SQR.

    So a function you’ve tried out in your spreadsheet won’t work as a macro. What a bunch of shambling morons.