Tag Archives: 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 …

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.

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 …

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.

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.