Hlookup

HLOOKUP

Whenever anyone thinks about a lookup function, the first thought is typically Vlookup. Sometimes however a Hlookup will do the trick. Both functions are virtually identical except that Vlookup searches vertically while the Hlookup - yes you guessed it- searches hortizontally.  ( For my purposes I deliberatley excluded discussion of Index Match for those of you wondering.)

In the example below, I am using the HLOOKUP function to retrieve the Total budgeted expense for whatever month happens to appear in cell C9.

Since I want to retrieve the Total which is at row 7  that becomes my row index number.

My table_array which I called budget_expenses is C1.N7.  You don't really need the table array since we are not copying the formula anywhere- but it is a good habit to get into.


I know that what I am looking for is in the table so FALSE is not really needed - but again it is a good habit to get into particularly if you can't type or spell :)


So, essentially, Excel looks at the table array, and looks across the top row for a match with what is in C9.
In my example, it is January. When it finds a matching column, it then goes down 7 rows and retrieves the value it finds.

1. Select the cell range C1:N7.

2. Name the cell range Budget_Expenses.

3. Click on cell C10 - this is where the answer will display.

4. Enter a HLOOKUP function in cell C10 that returns the Total value. Use Budget_Expenses as the table array and cell C9 as the lookup value.

(Hint: Total is the 7th row).

5. The answer for January should be $31,650. Change B9 to November and watch the total change.

If you use a Data Validation in C9, you will save yourself some time and typing errors down the road.

Go To Special

 Go To Special feature can be very useful in a variety of ways.
Many of the selections are pretty self explanatory. For instance, you can use it to jump to comments in a spreadsheet as well as formulas and errors. If you use the audit features in Excel, you can have it immediately jump  to precedent or dependent cells.

One that you may not see a use for is Visible Cells Only.
If you subtotal data and copy it elsewhere, all the data pastes(even the hidden data).
Okay- see where I am going with this?
If you select your subtotals and then select the Go To Special dialog box and select Visible Cells Only - if you then select Copy- only the visible cells are copied. So, when you Paste, only the visible subtotals are pasted.

Finding Conditional formats and data validations are also useful.

In Excel 2003, press F5 and it will take you to the Go To dialog box and the Special button is at the bottom of the dialog box. You can also find in under the Edit menu.
In Excel 2007, select Find and Select icon on the Home Tab and you will see Go To Special....

Hey, it's Friday. Have a great weekend.

I love to use Data Validation to create dropdown lists - particularly when working with Vlookups but I found it irritating that my source list had to be on the same sheet.  After being extremely lazy I finally got around to looking for a work-around and found one at Contextures.com. 

1. Create your list
2. Range Name it - (Select your data, click in the Name Box and type a name and then press Enter)

In this example, I selected my data and created a range name called product.The Name Box is directly above column A.

• Select a sheet and cell where you want the drop-down list to appear.
• Select the Data tab
• Select Data Validation
  
  Select List from the Allow: drop-down
  In the Source: section, type = product
• Click OK

  

Click on the drop-down and see the list you created.

Table of Contents is not just for Word!

Table of Contents

When you think of Table of Contents, you immediately think of  Microsoft Word but it can also be useful in Excel. I think I am showing my age with this one but I do believe that Excel 4 or Excel 5 allowed you to create a Table of Contents in a workbook. Well, we are up to Excel 7 ...and soon to be 10 if you can believe it and while it is not a feature you can still have a Table of Contents - you just have to create it. It is very easy as you can use hyperlinks.

Why a TOC ?A Table of Contents allows you to easily organize and pull your data together even if it is all over the place. It can be useful for yourself or if you share files with others. It is also a very useful way to navigate around if you are doing a presentation.  Additionally, it is a great way to find files that are linked externally or for that matter hidden worksheets.

In this example, I have a sheet that contains all my input or analysis, another with charts and then a sheet with other data I want to present.

I created a new sheet called TOC and this is where I want to put all my hyperlinks.

To Insert a Hyperlink within the document, click on a cell where you want the hyperlink to display

Click Insert>Hyperlink

In the first column called Link To:
Select Place in this Document

Select the sheet you want to link to by clicking on it and then type the cell reference in. (You need to know the cell you want to link to ahead of time)
In Text to display, type in a name to display in the cell otherwise the default is that it will display the sheet name and cell reference.
Click OK.
Yes, it is that easy. In the Hyperlink dialog box, you can also link to an existing file, existing webpage or even create a new document to link to.

Below is a picture of a TOC that I created.

  Notice that I even have a link to a PowerPoint presentation.

Hyperlinking is quick and efficient.

Just a word of caution, if you hyperlink to files on your computer or network and then do a presentation elsewhere - make sure the links work. Generally, you need have all the files with you if they are not hyperlinked to the Internet.

I hid the gridlines on TOC so that it would look better when I displayed it for an online presentation
Go to the View tab and in the Show/Hide Group, remove the checkmark from gridlines.






Have a great weekend. I am so glad it is Friday!

SUMPRODUCT using Conditionals

As I mentioned the other day, SUMPRODUCT is very powerful. If you looked at the earlier blog, you saw its original or basic use -Multiplying corresponding values in columns and then summing them. If you skipped that blog, you may want to go back and take a look at it first.

With SUMPRODUCT, you can do much more. There is an interesting discussion that explains in great detail how SumProduct works in case you are interested. http://www.xldynamic.com/source/xld.SUMPRODUCT.html
It is so good, that I don't want to repeat it but will just walk through an example.

I can do two different tasks with SUMPRODUCT 
First- Using the example on the left,  I can use it to add up all the Items Sold if the Cost per Item is equal to 2.

=SUMPRODUCT(((B2:B7=2))*(C2:C7))

The resulting answer is 2875. Reminds you of SumIF doesn't it?

Now, for those of you still using Excel 2003, yes- the good news is that you can have multiple conditions.  However, there is a twist to the syntax so it is a bit different.

To add up all the Items Sold if the Cost per Item is equal to 2 or equal to 1 you would use the following formula.
=SUMPRODUCT((B2:B7=2)+(B2:B7=1),C2:C7)

Notice that I am not using an asterisk * but a comma. If you use an asterisk you will get an incorrect answer. So, be careful.

All the conditions are considered part of Array 1.

 

   

 

Second task - Multiplying Corresponding Columns with a Conditional and then Summing

Anyone noticing that all of a sudden SUMPRODUCT is just summing?
What happened to the Product part of the function?

If I wanted to multiply all the Costs and the Units Sold if the Items equaled 2, the equation would look like this:

=SUMPRODUCT((B2:B7=2)*(C2:C7)*(B2:B7))

Interesting eh? Notice that the asterisk is back and that everything is considered part of Array1.

There is a lot to this function so take a look and play around with it.

Two-condition formulas: SUMIFS vs. SUMPRODUCT.(Microsoft Excel 7.0): An article from: Strategic Finance
Cheers! 

Erin go Braugh

Dance as if no one's watching, sing as if no one's listening, and live everyday as if it were your last.

Happy St. Patrick's Day.

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.