National Administrative Professionals day

Apparently yesterday was Administrative Professionals day. It made me think, who actually determines national days? Do our federal taxes actually pay a salary to someone out there who has the job of declaring April 27th "National Ant Rights awareness day?" We ask that young boys refrain from torching ants with their magnifying glasses on this day. Instead, present them with a small gift in appreciation for all their comrads who have fallen to the heat of that bright light in the past.

So, I've done my research. Administrative Professionals day is the Wednesday of Administrative Professionals week. The entire week (originally called National Secretaries week) was declared in 1952 by the US secretary of commerce Charles Sawyer.

Actually, there is no such thing as a "National Holiday." All holidays are determined by each state. For example, Patriot's Day (Boston Marathon day), in Massachusetts is not observed by other states. Most states observe the national holidays anyway though.

Only the President and Congress can legally designate a federal holiday for federal employees, then the states have the option of whether or not to adopt that holiday for their state employees.

So there's the story about national holidays. I'm going now so that I can write congress and petition for "National $2,000 hammer day." The holiday where we all go out and buy a $2,000 hammer from a company that we own in order to fund projects that we aren't really supposed to be funding.

Excel Vlookup function

Since I use this function every single day at work I thought it would be useful to post here, so that as my memory declines with age, I will always have a good reference for myself.

The vlookup function is used to find a value from the left hand column of a list and return a value from that column or any column to the right of it.

Simple Example (obviously I would do this easy thing without a formula, but I usually deal with 5,000 or more rows and you can see how difficult it would be if the list were longer than 9 rows):
Let's say I have this table and I am looking for the third column given the number 7. I can look in the table and see that that would be the letter "p". The vlookup formula is basically that. Here's how it looks in english: "=vlookup(what you are looking for, where you are looking, what column you want to see, the number zero (don't ask why, just put it there)". In this example the formula would look like this: "=vlookup(7,[the list reference],3,0)". This formula would give me the value "p". If I want the value "g" instead, I just change the ",3," to ",2,". If I want "k", I would change the "(7," to "(2,"

1	a	j
2	b	k
3	c	l
4	d	m
5	e	n
6	f	o
7	g	p
8	h	q
9	i	r

So now you know. Have fun. I'll post something good for the non-technical folks tomorrow.

Excel Pivot Tables

This weekend, my dad was talking about trying to learn how to use pivot tables, so here you go dad. This one's for you.

A pivot table is a fancy excel name for a list summary. For example, let's say I have an excel list. Columns A and B are last and first name respectively. Column C is age. Column D is Amount. So to create a summary of this data:

• Start on an empty worksheet
• Select: Tools --> Pivot Table & Pivot Chart report
• Choose Microsoft Excel list and Pivot table and click next
• Go to your list and select columns A - D
• Click next
• Click Finish

If you get an error that says "Pivot table field name is not valid", you probably are missing a column heading. Excel won't create a pivot table unless every column has a heading.

You will now have a template on the left and a "Pivot Table Field List" on the right. Now you can start dragging and dropping your fields from the field list into the four separate areas in your pivot table. The four areas are: Page fields, Row Fields, Column Fields, and Data fields. As you drag and drop fields into the pivot table, your field names will appear as gray boxes. You can drag these gray boxes around as much as you like until you get your table to look the way you want.

So, in my example above, if you just want to add up the amount. Drag amount to the data field.... Hey!! Now there's one of my real annoyances with excel pivot tables. It thinks I want to count amount instead of add it up. Well, we can fix that. Right click on the data or the gray box and select field settings from the dropdown box. Here I can tell the pivot table that I want a sum (or average if I had chosen the age field) and pretty much anything else.

Annoyance #2 - Pivot tables always think you want your data in rows if you have two or more things that you've dragged into the "data" section. I like my data in columns so drag the gray box from the top left corner of the table into the "column" area.

There you go. Play around with it for 15 to 20 minutes and you'll be a pivot table pro.

Tomorrow -- the VLOOKUP function

Why is the sky blue?

After two months of inactivity here, I figured it's high time to pick up this blog again. I actually started to write several blogs in the past few months but figured that nobody would be interested in what I have to say. Now that I've gotten over that and I realize that no one is interested in these random things, I've decided to write them anyway.

About a month ago I purchased a book from Barnes & Noble called "The Science Answer Book." Now during conversation I can impress my friends with my random knowledge of all things science. For instance, the next total solar eclipse visible in the US will occur in 2017 in a 200 mile line from Oregon to South Carolina. It was meant as a coffee table book. I'm probably one of the few that reads it before I go to bed at night.

Anyway, last night I was reading and refreshed my memory on a question that I'm sure I will get in the next 1 to 3 years. Why is the sky blue?

Interestingly enough, this question also lends itself to the question, why is the sun yellow? Well, here's the basic answer to both questions as well as why the colors change at sunset.

All the colors of the light spectrum have different wavelengths, red has the longest wavelength, blue has the shortest. When all the colors of light combine they produce white light. Since blue has the shortest wavelength it has the most opportunity to collide with the particles in our atmosphere on it's journey from the sun to our eyes. It does collide with the particles of our atmosphere, so the blue light rays from the sun bounce around in the atmosphere and end up coming at us from other areas of the sky, rather than straight from the sun. Since the blue light rays are dispersed, the only light rays left are red and green, which combine to make yellow light (hence the sun looks yellow). As the sun nears the horizon, the light has more atmosphere to travel through to reach us. More atmosphere means more light colliding with particles, both blue and green light are dispersed, leaving the sun looking red and the sky looking pale blue. When the sun reaches the horizon, the atmosphere is so thick that even some of the red light collides with the atmosphere and creates beautiful shades of purple.

Well, that's my random fact of the day. I'll try to keep up on posting here.