Dry land... hurray! I spent the last 2 days expecting Noah's Ark to come floating by.  My daughter and I checked out the waves coming into Rockport Harbor on Monday. Unbelievable. It is nice to get back to the Midwest though and actually see some sun and dryland.

I've been blogging a bit about other topics but thought I would head back and start chatting about Excel again.   Today, I wanted to mention SumProduct.


SumProduct is a little known but very powerful Excel function.

It multiplies corresponding components in the given arrays, and returns the sum of those products.

So, what does that mean in English?
SUMPRODUCT multiplies the corresponding cells in specified columns and then sums them.
Let's look at the example below:

Typically, if you wanted to know what your sales were for the products shown:

•  you would multiply C2 times D2
• then copy it down the column.
• then you would sum up the column and get 254.

Using Sumproduct will save you steps and time as the SUMPRODUCT formula will automatically multiply the 2 columns' corresponding cells together and then sum it.

Type =SumProduct(C2:C6,D2:D6) and then press CTRL+SHIFT+ENTER

The CTRL+SHIFT+ENTER creates the array and that is why you have the {} around the formula. The {} tells Excel this is an array. (Typing the {} in does not work).

Why does this work?  An array is considered to be a block of related cells that are treated as a group.


The syntax for SUMPRODUCT is: =Sumproduct(array1,array2…)

Sumproduct allows you to have up to 30 array dimensions.

Streaming Radio/Music Service

STREAMING RADIO/MUSIC SERVICE

I am off to Boston to celebrate my mother's 75th birthday so you won't hear from me again until next week.
I'm going to visit family and hopefully make it into the North End to Mike's Pastry ... one of the best pastry shops around.

I ended last week talking about Outlook and Google calendars and synchronization. I thought I would chat today about Pandora. Hopefully, everyone reading this has heard of Pandora. If you haven't and you enjoy music then this blog entry is for you. My husband had never heard of it until I mentioned it and now he is enthralled. ... well that may be too strong a word.
If you have kids, check it out and then show off your knowledge and see if they know about it. ( They probably do.)

Pandora is a streaming Internet based music/radio service. It has 2 levels of subscription :  Basic service, which is free and then it has a Premium subscription that allows you to avoid ads.
Basically, what Pandora does is allow you to create stations of your favorite arist or type of music. Pandora will then play that artist and other artists sharing a similiar musical style.  In addition, the site has different tabs that display the lyrics of the song, the history of the band and then tells you of similar artists. You can add/edit/rename the stations.
If you subscribe to it on your computer, there is nothing to download. Just login when you want to listen to the music. If you find something you like - you can of course buy it right there at Pandora via Itunes.
Pandora apps can be found on the Iphone, Itouch, Blackberry and other devices.

So, when you have a few minutes and nothing to do - check it out. The website is pandora.com.
 Everyone needs a little music in their lives. There are a number of similar stations out there so if Pandora doesn't do a lot for you but you like the concept check out Slacker.   It offers a bit more but I think it tends to be a bit more commercialized and advertisement-laden (that is solely my opinion).
If you aren't into music but into the technology and business side of startups etc. then you may find this NY Times article interesting . It is entitled How Pandora Avoided the JunkYard.

Cheers!

Synching Outlook and Google Calendars

Synching Outlook Calendar and Google Calendar

Sometimes I just take things for granted and assume everyone has the same technology knowledge- but in case you don't- you might be interested in this.

My new Palm Pre (which alas I had to return due to too many dropped calls and a design issue) did not work with Outlook. What to do?

Did you know that you can synch your Outlook calendar to the Google calendar and have them automatically synch as often as you wish and whichever direction.

The cool thing about Google Calendars is that you can share them with others and color code for each user. Google will also send you an email each day outlining your calendar for the day.
Palm believes in cloud technology which is why the Pixi and the Pre don't have to be manually synched on your computer anymore. I'm not sure Palm is going to make it - which is too bad- as I love their phones- but I think their technology direction is the way everything is heading.

Personally, I still like to use Outlook as it is more than just a calendar to me but I thought some of you might be interested.  For more information on this click here.

Have a great weekend. It is getting up to 55 degrees this weekend.  Yee- ha... maybe all the snow will finally melt.
Cheers.

Pivot Tables - Calculated Items

Pivot Tables - Calculated Items

After putting together my blog on pivot tables yesterday I checked back and realized that a couple of pivot tips have only appeared in my newsletter and were never published here. This one can be very useful and again is one of those "hidden" gems so I thought I would repeat it here.
This pivot table tip is from my December newsletter:
I had someone ask about joining items together in a pivot table. Most people are familiar with calculating values and formulas but not items. The answer is yes- it is very easy to add fields or items together in a pivot table.


Why would you want to add items together?If you recently consolidated sales offices, you might want to show a single consolidated entry in your pivot table. Or, perhaps you have 50 vendors and want to show the top 10 and then have a category called Other Vendors.

In the example below, I have a number of publishers including one called Collins and another one called Harper and Collins. I want to combine or merge the numbers of these two publishers into one for my pivot table.
Below are the steps to do it in a pivot table.


Creating a Calculated Item (Excel 2007)
Select an existing item (in this case one of the publishers)
Select the Pivot Table contextual toolbar and select the Options tab
Click the Formulas drop-down arrow
Select Calculated Item...

A dialog box will appear so that you can name your new item and create the formula

In Name: Type in a new name.
I typed Collins Consolidated 


In Formula: Select the items you want to use in the calculation and the operator.
My formula reads =Collins+"Harper and Collins"
(Excel automatically put the quotes around Harper and Collisn when I inserted them)

Click Add
Click Ok

Then go to your pivot table and click on the down arrow to find your new item. In my case Collins Consolidated.

Now I can deselect Collins and Harper and Collins and the pivot table will only show the newly created item Collins Consolidated.
 

In this way, I can remove the checkmarks from Collins and Harper and Collins and see the summarized information in All Collins in the pivot table.

The nice thing about this is that I can still display the 2 publishers separately if I want.
 

Pivot Tables- Show Differences

PIVOT TABLES - SHOW DIFFERENCES

Everyone knows how absolutely cool Pivot Tables are but not everyone is aware of some 'hidden"pivot table features. Today I wanted to talk about comparing values in a pivot table.  Frequently, you need to compare against a base budget number or you may want to compare last year against this year. The good news is that this is very easy to do with a pivot table.

Create your pivot table as you normally would

• Right-click on the pivot table
• Select Value Field Setting
• Click on the Show Value As tab
• Click the drop-down arrow and make your selection (% difference from, difference from,% of total)

 In the example, below, I changed Normal to Difference from and then selected

Year from the Base Field and selected as 2007 as the Base item.

The field you want to base the difference on must be included in the pivot table.Click OK

The resulting pivot table shows how 2008 and 2009 compare against 2007. Since 2007 is the base no numbers are displayed in the 2007 column.

While numbers show a lot, sometimes percentages are even more useful.

 Below is the same pivot table only I selected % Difference from 2007.