Getting Your Spreadsheet Roll On

Suprise, I didn’t do jack on Friday. Probably much like the rest of corporate America. However, my Monday will be much more productive and “Functional.” So guess what, today is the first of many quasi-planned Functional Monday’s. Functional Monday’s will be what happens when I start drinking wine and/or beer by around noon on Friday.

Today I want to talk a little bit about another pretty simple function, =Countif(). The general format is =Countif(CriteriaRange,Criteria). We are going to set up a little sample worksheet to play with =Countif().

In A1 we are going to put last Friday’s date “4/4/2008,” then in A2 we will put this Friday’s date “4/11/2008.” Now highlighting A1 and A2 designated A1:A2 in excel we can grab the black lower right corner, drag down and get the Friday dates for the rest of time. Now we have the potential days to slack off and get shalacked. Lets suppose for some reason you are in AA, maybe it is because you start drinking around noon everyday, idk. We are going to make this a journal of when you start drinking every Friday, or when I start drinking if you would prefer to project and swim in denial. I am going to enter “11:45 AM” in cell B2, cause well you know. Now we can make up a couple more sample times to start drinking for the rest of the month, say “1:00 PM”, “12:00 PM”, and “9:30 AM” (big day.)

Now in C1 we can enter the formula =Countif(B:B,”<=12:00 PM”). The “B:B” means we are considering all of the contents in the B column. This will return a 3. That means you/I started drinking before noon on Friday 3 times this month. That translates to an expected 3 Functional Monday’s instead of Fridays. This formula is very versatile in its use, but you will have to play with it some more to keep the ball rolling. While you are at it try =Countblank() and =Counta(). We will save those for another day, but god knows if you are reading this you have nothing better to do then count random cells in excel.

Its about time for a beer.

FunctionalMonday4-7-08

Somewhere in the world it is still Thursday. As for now, I am just posting a little animation someone came up with for me. I am going to be using it as the intro to my tutorials. If you don’t like it, too bad you are going to have to see it every time you want my wisdom. Which leads me to the more important issue, yes I got my microphone and we will be ready to rock and roll on Tuesday. So far it has been used to talk to someone in Turkey, works fine.

Anyway here is the little number that was put together:

Until about 12 hours from now, hold on to your knickers its almost Functional Friday!!

Thats right April fools is over and there is no first tutorial on exceltuts, my apologies. I ordered a mic from the egg, but it is slated to arrive tomorrow. Trust me a tutorial just isn’t the same if you can’t hear the manna from god that is my voice.

On the up side, I have no more excuses after this one. Oh in case you missed the glory that was YouTube’s April 1st shinannery, hit the glorious video from here:

Rick Roll

Outside of that little ditty I thought April 1st on the net was pretty boring, clearly I didn’t do anything to help that cause.  I however did decide on my first tutorial subject matter….nope, gotta wait.  Next week Tuesday will be the best cherry popping event on all of the internets.

See you then!

Got a little update today from Word Press that I am pretty happy about. They released a new version and it looks like it just got easier for me to upload videos, etc. That will definitely get the first use this Tututut Tuesday!

Figured since I was bummin’ cause it is Sunday (blah), I would do a short little follow up to the Functional Friday this week. There is another function, Weekday() that you can use in excel to output what day of the week a specific date is. The general format of the formula is =Weekday(Date,[Format]). Just as a reminder the brackets around the Format in the function mean it is an optional variable. It is legit to write the formula =Weekday(Date). However, the [Format] variable allows you to change on what “day” the week starts. The default is that a week runs Sunday to Saturday, not cool. That means excel will output “1″ if the date is a Sunday, “2″ if it is a Monday, etc, etc. I find it imperative to maximize what I consider the weekend. This means taking the #1 spot away from Sunday and giving it to Monday. To do this we would type the formula as follows =Weekday(Date,2). This will then return “1″ if the date is a Monday, “2″ if the day is a Tuesday, etc, etc. I should also mention that the “Date” portion has to be an Excel date serial number, or a link to a cell with Date format.

I truly loath Sunday, never really accomplish anything on the downhill slide to the following week:

=if(Weekday(Today(),2)=7,”I hate my life!”,”Could be worse”)

That’s right we are starting with good old date coding for Excel. Pretty basic stuff here, but don’t underestimate the importance of handling dates accurately. Not always the easiest thing, especially if you are a finance fan. The general format of the function is =Date(Year,Month,Day) simple as pie. What I like about this formula, it does formatting, and you can de-construct and reconstruct a year value incorporating changes at will. For example, say we are making a calendar and we want to create a table of what day we are going to shower every month. We are going to put today’s date in A1, cause I took my monthly shower today…I know all gussied up and no where to go. In A2 we use the formula =Date(Year(A1),Month(A1)+1,Day(A1)), this will return 4/28/2008 (the next time I can go out in public.) If you use the cursor in the corner of A2 and drag that formula down you can create years and years of days you will actually be clean. This is also great for adding days, the math with days gets much more hairy than months, but it made for a good example.

This leads to another good use, extracting information. Suppose we want to make that same chart also have the days in which we clean our bathroom. We only shower once a month, so once a year seems ample. In B1 we can go ahead and type =Date(Year(A1),1,1) . If we drag that down to the end or double click the corner box for auto expand we will get a whole bunch of first of the years. Clearly, my new years resolution every year is to clean the tub, I succeed. In the end you will always have the 1st of whatever year you are taking a shower. You could use this for more applicable things for doing bills, rent, and some other things I am sure. Using a reference date as one piece of a date you are creating allows for dynamic use of that date.

Alright, one more example how to use the date function. Now suppose your significant other is a major temporal hypochondriac, not sure how that works out with your showering only once a month, neither here nor there. However, their vice is knowing how many days it has been since things have been cleaned every time you all pony up and get in the tub. We can use the function Days360() with our properly formatted date cells. The general format is =Days360(Start Date, End Date). Very well, in cell C1 we can type =Days360(B1,A1) and out comes the number of days it has been since we had disinfected the shower, last time we used it. Figuring these things out is all in the nature of keeping your partner happy right.

Well there is the little summary for today =Date(2008,3,28) hope you know a little more about what you can do with properly formatting dates in Excel.

P.S. - I am going to tag all the posts with the formulas I use in the text, that way when this site gets all cluttered and nasty you can reference a function you are trying to use.

FunctionFriday3_28_2008.xls

Well you’ve either stumbled in from the far reaching nooks of the internet or you were actually trying to find us, probably not. Something to get off my chest (other than 3 hairs): I am in love with Excel. It can have my babies. It has really been a decade long love affair, since about the dawn of Office XP. I have flirted with Excel 2007, but I am pretty sure that 2007 is currently the ugly drunk side of Excel, some decent qualities…okay mostly its easy. However, for me I prefer the ripely aged 2003 version and that will be the home of our/my tutorials.

What I love about Excel, is playing within the simple rules and functions to find creative solutions to execute complicated tasks. In light of that, I can and have programmed in VBA for Excel quite a bit, but I feel it is cheating. To continue the analogy it is like throwing some Ruphies into the drink instead of sweet talking your way in. So if you have a programming question ask it, if you don’t you wont come up with any based on my work here.

What can we look forward to on ExcelTuts, well in general sarcasm. Outside of that I hope to start by warming up the kettle with two little ditties every week. Right now, I am shooting for Functional Friday, and Tututut Tuesday. Yes, those are lame kitschy alliterations, deal with it. Currently, I am thinking of using Adobe Captivate to do the deed, that means you’ll need Flash. If you don’t have it, get off my site. Actually click here, and welcome to the internet… Anyway, that means we will go for a full Tutorial on Tuesday and a Function on Friday (down and dirty, with some interesting positions, errr spreadsheets you can put it in?!?!)

Finally, please ask questions by posting comments. It will boost my ego to answer them, I will have someone to rip on, and you might get a good answer. Mostly, I will use questions to make the tutorials so everyone is getting return on putting something into the site.

Pretty sure that is enough geeky innuendo and intro to get everyone turned off for the night. Enjoy!