The Insert Function Icon

  This might seem basic but then again it may not. Have you ever created a complex IF statement that doesn't work? Probably. Now, how do you fix it? I usually see people delete it and start all over.
 Sometimes you may want to start all over and sometimes you have to start all over but  frequently you just need to edit a small component of your formula.

So, here is the easy way to edit your formula.

Click on the cell containing the formula and then click on the Insert Function icon on the formula entry bar.
This will automatically bring up the formula in the Functions Argument box.

 In the example below, I left out the quotation marks around snowshoes in my SumIF formula so the answer displayed was 0. To correct it, I just clicked on the fx icon and then added the quotation marks in the Functions Arguments box that popped up.

This works even if you originally typed the formula in manually. My example is pretty simple but if you have a nested IF statement or other complex function this is the best way to edit it.  Just be aware - if your formula has a huge logic error in it, Excel will not open the Functions Argument dialog box. 

If you have a complex function and are using multiple functions and want to fix a specific component of it, click on the function name in the formula entry area and than click on the fx icon and Excel will just bring up that part of the formula rather than displaying the entire formula. This works great if you have a nested IF and are trying to edit one of the IF statements.

SkyDrive and Office Web Apps

Wow. It's been awhile since I have blogged. - That's what a holiday vacation will do for you. I hope your holidays were fun.

We went and visited my family in Boston and of course we got to experience the Blizzard of 2010 which is always fun. Unlike New York, the area we were in was plowed and passable within a day though so that of course gave my daughter lots of time to shop. Because we just love the snow and cold so much we drove an extra 5 hours and spent a few days at Smugglers Notch in Vermont skiing. If you have kids, this is absolutely the best place - every year it is awarded all the prizes for Best Family Resort.  This year my daughter and I tried snowboarding. My muscles still ache and I now have a much greater respect for snowboarders than I had. I think that I will remain a skier though.

I did so some work related stuff while on vacation and that included some reading. I just finished a book called Using the Microsoft Office Web Apps and thought I would share some information about it for those of you who have not heard of the Web Apps or SkyDrive.

 SkyDrive is a free online storage platform that is available from Microsoft. It allows you to upload and then share files- PDF, video, Office documents, photos etc.

 

This should be of particular interest to small firms or sole proprietorships that can’t afford a lot of IT expense.
This is also great if you travel – you no longer have to panic if you can’t find your flash drive with your presentation on it or you need to suddenly access or share a file while on the road!

 

With Skydrive:

•  You get 25 GB free online storage
• You can create folders
• You an upload existing Office documents and in many cases create new office documents online using Office WebApps which are basically stripped down versions of Office applications.
• You can password protect your folders and then allow access to individuals or groups.

 To find out more about it, go to http://explore.live.com/windows-live-skydrive-get-started. You access everything through Windows Live so if you already have a Live email or a Hotmail email you can use that otherwise you have to create one. It has a lot of potential. It is a little work to set it up but you can access it from anywhere and share it with anyone. How cool is that.

 

Have a great weekend. 

Season's Greetings!

Merry Christmas and a Happy New Year.
Best wishes to all of you for the coming year.

I'll be back blogging after the New Year.

Patricia

When your X axis are numbers

You get to be my guineau pigs. I played around and created some Excel videos about a year ago and put a few on YouTube and then I just never got back to it.  However, I am finishing up a self-study CPE course on Excel Charting and thought that perhaps creating a selfstudy video course would be a nice course since charting is so visual. So, anyway, this is my first crack at it. 

In the video, I talk about the problems of charting when your X axis are numbers instead of text. As you can see from the screenshot below, the chart has a few problems and does not display the selected data in Column A and B very well. To see how to solve the problem, click on the video.

When you play the video, click the icon on the far right to get it to full screen mode.

Let me know what you think. If you can't access the video here, check it out on YouTube.

Extracting Text of Different Lengths

Use the Search function to separate text

Most people are familiar with LEFT, RIGHT and MID as ways to separate text and they are useful -
but only when all the cells have the same number of characters.

If you want to separate employees' names or emails you can't use LEFT, RIGHT and MID by themselves because all the names and emails are different lengths. This is where the SEARCH command comes in.

In the example below, I want to separate my first name from my last name so to do that I need to combine the LEFT function and the Search function.

The actual function is =LEFT(A2,Search(" ",A2)-1.

Excel searches A2 for a space as denoted by the empty quotation marks, in the formula, and finds it between my first and last name so at this point the formula would read as =Left(A2, 9-1)

So, what does that mean? Excel searched and it found the empty space. This empty space is the 9th character. We subtract 1 to tell Excel we only want 8 characters. The LEFT function then retrieves the 8 characters to the left in cell A2 and returns my name Patricia.

That was the easy one. Retrieving the last name is a little bit more work. In this case, I am using MID and SEARCH.In case you are not familiar with MID, the syntax is =MID(cell reference, start_number, number of characters)

The formula is =MID(A2,SEARCH(" ",A2)+1,35).

Excel reads that as =MID(A2,10,35)

To break the formula down, I again told it to search for the empty space between the first and last name and to use that as the starting point. Then, I told it to go over 1 character and return the next 35 characters. I arbitrarily picked 35 since few last names would have more than 35 characters.

Below are examples, if you wanted to pull apart an email address.

We finally have enough fans to have our own Facebook URL. If you have a chance, check it out and click on the LIKE button while you are there.  If you Facebook, all of these blog entries also appear there as do my tweets.  It is like one -stop shopping!

http://www.facebook.com/cpaselfstudy

I am watching tiny snow flakes coming down as I type this. Have a good week and stay warm.