Excel Tips from MrExcel

Online Edition of Inc Magazine

2009-07-01T16:44:00.001-04:00

Very cool - Howard Greenstein from Inc Magazine wrote about MrExcel.com in his blog today. Check it out at: http://blog.inc.com/start-up/

Rounding Per Sixth Pay Commission

2009-06-16T06:13:00.002-04:00

The VIth Pay Commission of the Government of India is recommending a fairly complex rounding scenario. If the amount in excess of the last 10 is up to and including 0.99, the amount should be rounded down to the previous 10. All other amounts should be rounded up to the next 10.

Say that you have a pay amount in A2.
Use =MOD(A2,10) to calculate the amount in excess of the last 10.
If that amount is less than or equal to 0.99, then use =INT(A2) to calculate the pay.
Otherwise, use =ROUNDUP(A2,-1) to round up to the next 10.

Note that ROUNDUP requires some special handling. If you are using Excel 2007 or newer, the function will always work. However, in Excel 2003 or earlier, the function might evaluate to a NAME error. In that case, try these simple steps:
1) Open Excel
2) From the Tools menu, choose Add-Ins
3) Make sure the entry for Analysis ToolPak is checkmarked
4) Click OK
5) Re-enter the formula or recalculate the worksheet

Putting all of the formula pieces together yields this formula:
=IF(MOD(A2,10)<=0.99,INT(A2),ROUNDUP(A2,-1))

Report from Microsoft

2009-03-06T05:16:00.004-05:00

Awesome. I spent a few days at Microsoft this week, at the Microsoft MVP Summit. This is a great opportunity for me to see what is coming in Excel 14 and Excel 15. Now, unfortunately, I am under NDA, so I can't tell you what I've seen. HOWEVER, I can point you to a public video that will give you a teaser of two things that Microsoft has planned.

1) Pivot table fans: Are you ever frustrated with the Page Filters? In Excel 2007, you can open the page filter and select multiple items. However, the resulting pivot table now shows a heading of "Multiple Items". This is rather confusing, because when you print the report, you don't know what items are selected.

If you've had this frustration, open the following video and grab the scrollbar and fast forward to the 1 hour and 32 minute mark. You will see something that the presenter describes as "slicers" - they seem to be a visual layout of the old Page Field area.

http://tinyurl.com/cssvcj

2) In the same video, at the 1 hour and 27 minute mark, Donald Farmer talks about a new Excel add-in called Gemini. This is the awesome part. In the video, Donald mashes up a 20 million row data set with data that he downloaded from the web. The resulting Gemini-based pivot table allows you to easily join the data on the two worksheets and create a pivot table. This is a very exciting product for those of you doing business intelligence work in Excel.

There is a lot more that I learned this week, but again, it is all under NDA. Until I can find where Microsoft says it publicly, I will have to keep that under wraps. Gemini promises to make Excel 2010 a very compelling upgrade. Stay tuned. I will let you know when a beta becomes available.

GetPivotData in Excel

2008-11-04T06:51:00.002-05:00

People are passionate about GetPivotData. Most people hate it and want to turn it off. Some people embrace it and couldn't live without it. Whichever side you are on, I've written an article with the details. You can learn how to turn it off or the details of how to actually use the function. For the complete article: GetPivotData in Excel

PhoneTag Types Your Voicemails!

2008-10-31T22:34:00.002-04:00

Before starting MrExcel, I worked for 12 years in the corporate world. Over those 12 years, I had a number of administrative assistants. Once...for about 3 months...a young admin named Jennifer answered my phone. She would listen to my voicemails and type them.

Being able to read voicemails instead of listening to them was simply the most decadant service I've ever had. You can scan the written text and figure out which are important instead of listening to 10 minutes of voicemails.

Unfortunately, Jennifer moved on. On Vicki's first day, in her first 15 minutes, she announced... Look I know that Jennifer used to type your voicemails and I want you to know that I do NOT do that. Drat!

If you want to experience the decadence of having your voicemails transcribed, it is now 100% automated. Sign up for a free 30-day trial at PhoneTag.com. I just received my first text voicemail. Amazing. The best service ever. Do it. Now. You'll never go back. The service runs from $29.95 a month (unlimited) to 35 cents a message.

GoToMeeting outage Ruins MrExcel Webinar

2008-10-30T16:07:00.003-04:00

I have to apologize to everyone. When we tried to sign in to the meeting at 11AM EDT today, there was a message that GoToMeeting.com was temporarily down. I sent out a note on Twitter (my userid is MrExcel) to keep trying and answered about 10 e-mails that I would keep trying.

Around 11:05 AM, we got in, and many of you were trying because the attendance count quickly shot up to 70, then over 100, finally about 130.

From there, the reports are varied. Some instantly had no audio. Some could watch for about five minutes. For me, everything locked up after 18 minutes.

I currently have 50+ messages in my inbox saying that you were kicked out of the event.

I am disappointed in Citrix's response. There was a new build that went live this morning that was unstable. The recording of the webinar was lost. They claimed that this has never happened in 2008, but a search of the web shows similar problems in August. Update from 10/31/08...after I pointed out to journalists a TWIT and InfoWeek the response, I received an excellent apology from Citrix, along with a credit for a half-a-year of GoToWebinar. They said that they were taking steps to ensure this never happens again.

Many ask if I will reschedule? I do want to test again before the 11/21/08 10th anniversary webinar. I might try it again, record it, and then post the recording. I would rather not waste the time of 100+ people again. Update, again from 10/31/2008: The recordings are now live at http://www.mrexcel.com/webinar.html

AC/DC Video in Excel

2008-10-29T18:46:00.004-04:00

This workbook by Phil Clandillon is an amazing thing. It plays 45 seconds of an AC/DC rock video. The workbook advertises that you can watch the video in glorious ExcelVision, which is a very low-res video rendered in ascii text.

First, there are no viruses in the code. Phil left the code open for anyone to see. If you are a VBA fan, take a look at the code. Phil was able to extract the .WAV file embedded in the Excel workbook to your hard drive using a clever bit of code in the ExtractWAV procedure.

Download the workbook from http://www.acdcrocks.com/excel/

To discuss the code, visit http://www.mrexcel.com/forum/showthread.php?p=1730018

You have until April 2014...

2008-09-26T07:01:00.002-04:00

While Microsoft is dropping support for the SP2 version of Office 2003, they are still supporting Office 2003 SP3. Based on the product life cycle rules, they will keep supporting Office 2003 until April 2014. So - for those of you happy to hold on to File, Edit, View, Insert, Format, Tools, Data, Window and Help, you have a while before you will have to upgrade!

Where is the MrExcel Community?

2008-07-16T07:30:00.004-04:00

I published a couple of Widgenie.com charts in the blog and this struck up an interesting conversation in the message board about the accuracy of Google Analytics data. If you are reading this and would like to add yourself to http://www.mrexcel.com/map.html, give it a try. Zoom in and click on your location. You will get to add a name, a short message (something like Hello from Akron!) and choose a color for your marker.

The MrExcel Community - By Country

2008-07-15T13:07:00.005-04:00

Here is an interesting analysis based on the number of visits to MrExcel.com in the first six months of the year. The data is from Google Analytics and the Chart was created with the free Widgenie.com beta site.

I suspected that the USA, UK, Canada, and Australia would be the leading sources of visitors. But I wonder about the rest of the list. The first potential glitch... Google identifies 226 territories, yet I generally think about there being 191 countries. I am not sure where the extra 30+ territories come from. Can you spot any other surprising statistics? To comment, visit this message board post.

Widgenie

2008-07-13T09:46:00.002-04:00

Check out this cool internet graph that I made in a few clicks using the new WidGenie.com site. They are still in beta, but it creates nice embeddable graphs. I created this graph for episode 805 of the MrExcel podcast.

Appearing on The Tech Guy Labs Radio today

2008-03-08T05:46:00.001-05:00

For those of you in the U.S. with XM radio or in any of these 37 cities, I will be making a guest appearance on Leo Laporte's radio show a little after 3:30 PM eastern time.

College Bowl Mania Winners

2008-01-08T06:38:00.000-05:00

We had 33 people join the MrExcel group at the ESPN College Bowl Mania. I promised autographed books to the three highest scores.

Tom Urtis came in first with an amazing 26-6 record.

D. Farris and L. Ciavala finished 2nd and 3rd. I've sent notes to everyone and will post back here to let you know what books they chose.

I finished nearly last, 32nd with a dismal 14-18 record.

Christmas List in Excel

2007-12-17T09:16:00.001-05:00

On December 11th, St Louis Post Dispatch columnist Bob Rybarczyk suggests that Santa should be organizing his naughty and nice lists in a spreadsheet. Very very funny. Check it out at http://www.stltoday.com/stltoday/lifestyle/columnists.nsf/suburbanfringe/story/D58539B32849D775862573AE005CE51D?OpenDocument

College Football Mania

2007-12-11T06:29:00.000-05:00

If you are a fan of American College Football, join the MrExcel.com group at ESPN's College Football Mania. I will donate autographed books as prizes for the top three scores in our group. It is free to play. Sign up at
http://games.espn.go.com/bowlmania/group?groupID=16283

Scroll Wheel in VBA Editor

2007-12-01T06:37:00.000-05:00

A long-time frustration is that the mouse scroll wheel does not scroll the code window in the Excel VBA Editor. During a recent seminar in Boise Idaho, Brent pointed me to a tiny application that allows the mouse wheel to work in VBA. Check out the software at: http://www.gasanov.net/VBScroll.asp

131 Productivity Boosters

2007-10-21T07:18:00.000-04:00

Rich McIver has published a list of resources for Excel, Word, E-Mail and Instant Messaging. If you are looking for new sites to visit, check out his list.

Pasting Excel to PowerPoint

2007-09-29T14:04:00.000-04:00

If you frequently need to transfer charts or ranges to PowerPoint, Avi Benita has produced a new add-in which should help. Using the EzPaste add-in, you can define which ranges or charts should be transfered to PPT. You can either have them pasted to new slides or have specific ranges get pasted to specific slides. If you think about it, this could be a great tool when you have to frequently update the weekly status report in PowerPoint.

On a side note, Avi mentioned that folks from the MrExcel Message Board assisted along the way in creation of the product. Check out www.EzPaste.net.

The Great Lakes Geek

2007-09-03T17:05:00.000-04:00

Dan Hanson calls himself the Great Lakes Geek. I had a great audio interview about the history of spreadsheets with Dan. Dan is podcasting this interview over several episodes of his podcast. Links are here.

Learn Excel from MrExcel...in Chinese!

2007-07-24T15:52:00.000-04:00

This is pretty wild - in yesterday's mail, I received a box with five copies of Learn Excel from MrExcel translated to Chinese. If you happen to be a native Chinese speaker, check out the new title from Posts & Telecom Press.

Excel Formula Auditing Tricks

2007-06-29T15:24:00.000-04:00

On today's episode of The Lab with Leo, I show off some of the cool formula auditing tricks in Excel. From the ability to see all formulas at once to cool features on the Formula Auditing toolbar, you can keep your formulas under control. View the show notes page to watch the episode.

Wild & Weird Functions in Excel on The Lab

2007-06-18T15:21:00.000-04:00

In today's episode of The Lab with Leo Laporte, I take a look at some of the bizarre functions within Excel 2007. Visit the show notes page to view the segment on your computer.

Overview of Excel 2007 Charting on The Lab with Leo Laporte

2007-06-12T15:18:00.000-04:00

Leo Laporte is back! The old Call for Help television show has been rebranded as The Lab with Leo Laporte. I visited their new studio in Vancouver to discuss changes to Excel Charting.

Visit the show notes to view the segment on your computer.

International Postal Rates Dramatically Increase

2007-06-04T19:23:00.000-04:00

The price of a first class stamp went up by 2 cents recently. The postal service also made dizzying and staggering changes to international parcel rates. Surface mail is no longer an option. Some countries still have Airmail and Priority mail, but other countries only have the higher Priority mail rates.

My Power Excel Bundle weighs 11 pounds and would cost $56 to ship to England.

I sell books at MrExcel and we do ship around the world. I appreciate when someone buys a book directly from me. However, with the recent postal increases, it is becoming more difficult to reasonably ship books around the world.

If you are buying a single book or CD, it probably makes more sense for you to buy the book locally. If you are buying an entire bundle, the price at MrExcel might save you enough to pay for the shipping.

If you are in Canada, I have set up ExcelBookstore.CA. This website will stock most of my titles but the orders will be shipped from Canada.

If you are in the United Kingdom, a similar site: ExcelBookstore.co.uk will ship orders from England.

If you are somewhere else, you might check to see if Independent Publishers Group has a distributor in your area. Scroll down to the bottom of this page. Your local bookseller can order the book from the nearest distributor on that page.

Now - we are still happy to ship to anywhere in the world, but you have to do what makes the most economic sense for you. If one of these websites can get you the book cheaper and faster, by all means, please take advantage of them.

Need better Stats functions work? Try Mathematica!

2007-05-16T13:07:00.000-04:00

If you are ever frustrated with Excel's statistical tools, check out the new version 6 of Mathematica. For more information, visit this post from the Long Tail.