Online Edition of Inc Magazine

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

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

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

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!

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

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

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...

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?

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

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.