Example of a Mash-up in PowerPivot

I just emerged from a marathon 33-day writing session where I finished five book manuscripts. One of those books is about a hot new add-in for Excel 2010 called PowerPivot.

For an example of the types of data that you can mash up with PowerPivot, take a look at my guest blog post at PowerPivotPro.com.

And, if you happen to be reading this before February 14, please consider voting for my entry here.

Jilted by YouTube Without An Explanation

I am suffering that post-breakup emotional hangover this morning. Yesterday, I learned that I was permanently disabled from YouTube for violating community standards. All of 1,065 videos are gone. I am forbidden from ever setting up an account at YouTube again. I had a long relationship with YouTube. I started podcasting back in 2005 and signed up for YouTube shortly after Amber MacArthur mentioned it on the set of Call For Help in Toronto.

There was no warning. No e-mail asking me to change my behavior. One day, everything is fine. The next day, my account is marked as permanently disabled.

At first, I figured it had to be some massive mistake. An automated program, left unattended on Christmas had wiped out a bunch of accounts. But a visit to search.twitter.com found only three other people noting that they have been permanently disabled in the last 24 hours.

When you go to YouTube help, they diagnose the "Your Account Has Been Permanently Disabled" message with this:

There are a variety of reasons an account may be terminated. The most common reason is repeated violation of the Community Guidelines or Terms of Use (including claims of copyright infringement). For severe abuse, it is possible for an account to be terminated on a first offense, such as predatory behavior or spam. Individuals whose account has been terminated are prohibited from creating new accounts. If a new account is discovered, it too may be terminated without warning.

There are two reasons that I could be terminated without warning: predatory behavior? Spam? I am not sure how Excel tutorials can be predatory. I haven't spammed anyone.

I asked for more information. They replied with:

Thanks for your email. Your "bjele123" account has been found to have
violated our Community Guidelines. Your account has now been terminated.
Please be aware that you are prohibited from accessing, possessing or
creating any other YouTube accounts.

YouTube staff review flagged videos 24 hours a day, seven days a week to
determine whether they violate our Community Guidelines. When a video or
account is brought to our attention we investigate and take action if
necessary.

We are unable to provide specific detail regarding your account suspension
or your video's removal. For more information on our what we consider
inappropriate content or conduct while using YouTube, please visit our
Community Guidelines and Tips and our Help Center article 92486.
Regards,


The YouTube Team

The answer 92486 talks about getting a series of three strikes before you are banished. I've received none. We then have the rather ominous "Serious safety and privacy violations such as threats and harassment may result in immediate termination without prior warning." What? What could I have possible done that is considered unsafe or threatening or harassing? Well...there was that one time that I suggested using INDEX/MATCH instead of VLOOKUP, but that can hardly be considered unsafe.

The community guidelines prevents porn, animal abuse, bomb making, graphic violence, hate speech, predatory behavior, threats. I've been fully clothed and filmed from the shoulders up in all of my videos. No animals were harmed. I do hate the way that double-clicking the fill handle fails when it encounters a blank cell in the adjacent column, but I don't think that I ever suggested violence over it.

They define spam as "Don’t create misleading descriptions, tags, titles or thumbnails in order to increase views. It's not okay to post large amounts of untargeted, unwanted or repetitive content, including comments and private messages." I have repeatedly taught people how to do things in Excel and I am guessing the double-click the fill handle trick probably came up three times in the 1065 videos.

They talk about copyright. I created all of my videos. I licensed the theme music for the first 500 episodes and then hired George Wood to write the three themes that we've used since then. The Dueling Excel theme sounds reminiscent of the song Dueling Banjos and another theme uses the same chord progression as Born to Run. Is there any chance that the Boss himself complained about my Excel videos? Bruce...what are you doing watching Excel videos?

I exchanged an e-mail with Leo Laporte. Leo wondered if my gmail account had been hacked. We just recently had an employee resign, so all of the passwords were reset with new hard-to-break passwords about three weeks ago. If there is any time that I feel that I have safe passwords, this is it.

In reading the YouTube terms of service, item 4D prevents commercial use of YouTube. But item 4E says that I can use YouTube to promote my business or enterprise. The first 1040 videos all included a closing slide that suggested people could pick up my Learn Excel from MrExcel book for more tips like this. Recently, I cut the 15 second theme down to 8 seconds and included a 6 second "Support for the MrExcel Podcast is provided by Easy-XL" voiceover.

Item 7 in the Terms of Service says that they can terminate people who are repeat infringers. YouTube gets to decide if something is pornographic, Obscene, Defamatory, or of excessive length. Are they getting me on excessive length? Over the four years, you have 1065 videos times 3 minutes which does add up to 53 hours of free Excel tutorials. Microsoft awarded me as an MVP for that content; perhaps YouTube thinks it is enough for permanent banishment.

Bottom line, I've been jilted by YouTube and there is no good break-up letter. Tell me I snore in the middle of the night. Tell me that you wish I would rather be promoting Google Docs instead of Excel. Tell me some good reason for dissing me for life. Right now, I really would like some closure to understand why YouTube is banning me forever.

I've spent just over four years producing five video tutorials a week. I employed a person to edit videos which cost tens of thousands of dollars over time. I've donated my time in going through e-mailed questions, finding suitable questions, and recording videos every week. There is nothing controversial here. I just don't get it.

The good news...YouTube is a very tiny piece of the audience. Most of my viewers come from iTunes or Libsyn or HowCast or Graspr. Losing YouTube will not put a dent in the podcast viewership. It just doesn't make sense. No trial. No jury. No explanation. Me, and my videos, are just no longer welcome at YouTube.

Easy-XL Adds 50 New Data Analysis Tools to Excel

Today, I am launching the new Easy-XL software suite. This add-in works with Excel 2000 through Excel 2010 and adds 50 powerful data analysis features to Excel.

As I see it, there are two markets for this utility:
1) People who need to do data analysis, but hate VLOOKUPs. If VLOOKUPs give you a headache, you will LOVE Easy-XL. You will find yourself doing things that normally take several hours in a minute.

2) People who can do VLOOKUPs with their eyes closed. Yes...I can whip out VLOOKUPs, but after having Easy-XL on my desk for a while, I started to realize, why go through the hassle of typing the VLOOKUP when Easy-XL can do the table join in seconds?

You can try out the software free for 30 days. Check it out at Easy-XL.com.

Power Pivot

There is a new add-in coming for Excel 2010 that will take pivot tables to the next level.

You might have heard me mention something about Project Gemini. This has now officially be named PowerPivot. Watch details about the project in Rob Collie's blog at http://powerpivotpro.com/

Hello Technorati...

6rvgcsbaw

OK - this was a bizarre post. We were trying to claim this blog on technorati, and they needed proof that I had control of the blog. So, they ask me to put in a post with those characters above. I would just ignore this post....

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.