Details about upcoming Power Pivot course (and a bonus tip on dashboards)

Hello friends.., I have 2 things for you: Details about our upcoming Power Pivot courses A short video about designing better dashboards. Details about the Power Pivot course: What is this course? Power Pivot, an Excel add-in makes it easy to connect, analyze & visualize massive amounts of data. Since this is a vast ocean of features and possibilities, I have created 3 different courses. Excel School: teaches you how to analyze data, create pivot tables, make charts, design interactive dashboards using Excel. Power Pivot Course: teaches you how to use Power Pivot to analyze data, create advanced reports & prepare dashboards all from familiar interface of Excel. Advanced Power Pivot Course: Deep dives you in to the world of measures, formulas, analysis techniques and re-usable patterns. You will also learn about performance optimization, custom calendars and other such potent data magic. (this will be taught by Rob Collie). Who should go for these courses? This course is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers. For Excel School, you can be a beginner or intermediate level Excel user with Excel 2007 or above. For Power Pivot courses: you should be familiar with Excel & Pivot Tables and running at least Excel 2010 to enjoy this course. NOTE: Go for bundled courses to save $s and become 2x (or 3x) awesome in your work. Course brochure & Lesson Plans Download course brochure Detailed Lesson Plans Excel School Power Pivot Advanced Power Pivot Use these to, Ask your company / boss to sponsor your for this course Understand topics & benefits of the course Learn how our course works Know about your teachers – Chandoo & Rob Find out how to get in touch with me if you have doubts or questions See what our earlier students think about us Know your Teachers Rob Collie: The PowerPivotPro, author, blogger, Microsoft MVP, Ex. Microsoft Program Manager (for Power Pivot) and my good friend, Rob Collie will be teaching the Advanced Power Pivot class. I could not think of any one else better qualified in this world to teach these advanced concepts and techniques to us. Chandoo: Well, you know me. I will be teaching the Excel School, Power Pivot & Dashboard portions of the class. IMPORTANT DATES Enrollment for course begins on: 1 August 2013 (Thursday) Enrollment closes on: 16 August 2013 (Friday) Classes begin on: 19 August 2013 (Monday) How much is it? Each of the 3 courses are $247 per participant. You can bundle any two courses for $447 (save $47). You can bundle all 3 courses for $647 (save $94) For detailed pricing, see the course brochure. Demo lessons For free demo lessons on Power Pivot, click here. Questions or doubts? If you have any questions or doubts about our course, please email me at chandoo.d@gmail.com or call me at +1 216 527 8597. Bonus Video: A tip for designing better dashboards Do you know that there is a simple way to break your dashboards in to manageable chunks so that you can work faster, create better looking dashboards and manage changes with ease? It is simple. Divide your dashboard in to 3 buckets. – Data, Calculations & Output. Watch below video to understand this technique and how it can help you. [See it on our Youtube channel or our website] Bonus: A 4 part tutorial on the dashboard shown in video Thank you Thank you for taking time to learn from us. We (Rob, I and rest of us at Chandoo.org) really grateful to you for making us your partners in your journey for awesomeness. We hope to see you in our upcoming Power Pivot & Excel class.

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 4.)

Welcome back to another gripping episode of “When good formulas go GREAT”.  Just like the immortal combatants in the classic 1986 movie Highlander, over the last three posts in this series our Excel nerds have been locked in an ages-old battle to decapitate. A text-string, that is. Not each other. So far we have seen some formidable formulas from these fearsome foes: Jeff: Formula Challenge 001 – Part 1 Sajan: Formula Challenge 001 – Part 2 Haseeb: Formula Challenge 001 – Part 3 But while we’ve witnessed a few intriguing battles, none of our defeated warriors ultimately had what it took to win the prize. So steel your nerves, grab a seat near the edge of the arena, and find out what brute force another contender can bring to bear on the problem in… …Part 4: The Sorcerer Samurai By way of a quick refresher, our formula challenge calls for a mighty Excel hero to slay a fearsome dragon – err, text string– and return triumphantly back to Court with its tail – err, substring – in time for the cook (that would be me) to serve it up to the King (that would be Chandoo) at the Feast of St. Christopher. Those dragons and associated tails look a little something like this: Dragon: Tail: NewZealand99Australia41 Australia41 France12NewZealand41 NewZealand41 Australia23France17 France17 England53France37 France37 England7NewZealand27 NewZealand27 Australia16England24 England24 SouthAfrica21France11 France11 SouthAfrica42Australia33 Australia33 NewZealand48SouthAfrica34 SouthAfrica34 England12SouthAfrica22 SouthAfrica22 (Bonus Question: Who can tell me what these numbers represent?) You can download the challenge and full dataset here: Formula Challenge 1.4 So our brave Knights must wield their mighty sword – Excelibur – and lop off the bit in bold at the end. The winner of the challenge is the combatant who can do that slicing with as short a sword – err, formula – as possible. That winner receives his or her weight in gold. Fool’s gold.  And as a wise Highlander one said about the winner of battles involving mass decapitations: “There can be only one”. But this will be harder than it looks. Those numbers are of variable length (and getting longer each year), and the fact that we are searching for a block of text and numbers that occurs after another block of text and numbers makes it very tricky indeed. Mwah ha ha. Well, the next contender for the prize is….SAJAN! Again!! You might remember Sajan the Magician’s vaguely coherent incantation of a formula from a few posts ago: =MID(A1,MODE(MMULT((N(ISNUMBER(-MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))={1,0})*(ROW(INDIRECT(“1:”&LEN(A1)))-{0,1}),{1;1}))+1,LEN(A1)) Discontent with the mere Bronze that his wizardry brought him, Sajan’s kicked over his magic cauldron, drawn his trusty pen-knife of a formula from its dusty scabbard, and stabbed our string right through the heart. Dead. With this: =MID(A1,MATCH(1,MMULT(-ISERR(-MID(A1,ROW(OFFSET(A$1,,,LEN(A1))),{1,2})),{1;-1}),)+1,6^6) Wow! From 133 characters down to 88! I can still hear the chants of “Long Live Sajan the Samurai” ringing in my ears from when he dragged that severed substring into the Chandoo.org forum and dumped it victoriously at the formula challenge thread’s  feet . Err, footer. But you weren’t there to see it, were you? So let’s have our Court Jester (me) don his silly face, and re-enact the fight blow by blow, so to speak. En Garde! 1. Samurai Sajan sneaks up on the string and slices it into slender slivers: He carves the string into one-character off-cuts, and casts them into a couple of columns with his crescent-shaped cutlass: =MID(A1,ROW(OFFSET(A$1,,,LEN(A1))),{1,2}) { “N”, “Ne”; “e”, “ew”; “w”, “wZ”; “Z”, “Ze”; “e”, “ea”; “a”, “al”; “l”, “la”; “a”, “an”; “n”, “nd”; “d”, “d9″; “9″, “99″; “9″, “9A”; “A”, “Au”; “u”, “us”; “s”, “st”; “t”, “tr”; “r”, “ra”; “a”, “al”; “l”, “li”; “i”, “ia”; “a”, “a4″; “4″, “41″; “1″, “1″} So he’s taken the original 1D string,and split it apart into a 2D (i.e. two column) array with the help of the {1,2} bit. Both columns of the new array are almost the same, except all the elements in one of them is one character long, and all the elements in the other are two characters long. If entered over a range, here’s what that looks like: 3. He displays any of dem digits dat looks like a digit as a digit, using a dash: Currently Excel thinks that this array is just text – even the numbers in it. Putting a minus sign in the front of the array – or in fact doing any kind of mathematical operation on it – will force any numbers to wake up and act like numbers. (Unfortunately, it also freaks any actual text out…to the point that any bits of text whinges “I’m not a number, there’s been some kind of error”. Hence the #VALUE! errors below, along with the numbers.) = -{ “N”, “Ne”; “e”, “ew”; “w”, “wZ”; “Z”, “Ze”; “e”, “ea”; “a”, “al”; “l”, “la”; “a”, “an”; “n”, “nd”; “d”, “d9″; “9″, “99″; “9″, “9A”; “A”, “Au”; “u”, “us”; “s”, “st”; “t”, “tr”; “r”, “ra”; “a”, “al”; “l”, “li”; “i”, “ia”; “a”, “a4″; “4″, “41″; “1″, “1″} ={ #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; -9, -99; -9, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; -4, -41; -1, -1} Again, here’s how that looks if entered over an Excel range (with our original string split apart down the side by way of reference): 4. He hacked away at the resulting horrible herrors (err..errors) that happened due to this harsh handling: He turns any of those errors to TRUE and anything else to FALSE with this: =ISERR({ #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; -9, -99; -9, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; -4, -41; -1, -1} ={ TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; FALSE, FALSE; FALSE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; FALSE, FALSE; FALSE, FALSE} So now we have TRUE where there was text, and FALSE where there was numbers. Which looks like this: 5. He butchered those Boolean values into bite-sized bits, by binding a brutal minus sign on at the beginning: Just as putting a minus sign in the front in formula 3 above forced any numbers stored as text to act like numbers, putting a minus sign in front of an array of TRUE and FALSE values (otherwise known as Boolean values) or doing any other kind of mathematical operation on the array will turn those TRUE and FALSE values to numerical values – in this case because it is a minus sign they will turn to -1 and zero. Abracadabra. =-{ TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; FALSE, FALSE; FALSE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; TRUE, TRUE; FALSE, FALSE; FALSE, FALSE} ={-1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; 0, 0; 0, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1 ,-1; -1, -1; -1, -1; -1, -1; 0, 0; 0, 0} …which looks like this: Note something interesting…the highlighted row above falls just before the string that we’re after. And it’s the only row in the entire array that has a zero in the first column and a negative one in the second column. So if we can work out where that combination of zero and negative one falls, we know exactly where our desired substring starts. 6. He magically multiplies the first array column by one, and the second array column by minus one, then maniacally mashes them together en masse: He does this by using the MMULT function with a 2nd argument of {1;-1}. =MMULT(={-1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; 0, 0; 0, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1, -1; -1 ,-1; -1, -1; -1, -1; -1, -1; 0, 0; 0, 0},{1;-1}) ={0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0} …which looks like this: MMULT is really hard to explain. It’s kind of like an 2-dimensional SUMPRODUCT. (Maybe it’s exactly like a 2-dimensional SUMPRODUCT).  There’s a great visual explanation at Mike Girvin’s ExcelIsFun YouTube channel athttp://www.youtube.com/watch?v=qJnL5hFfcYo Given our array in this instance has two columns and our second argument of MMULT is {1;-1}, MMULT in this case effectively multiplies each number in the first column by positive one, multiplies each number in that 2nd column by negative one -  and then adds the result together.  Multiplying the first array by positive one does nothing to it. Multiplying the second array by negative one changes any existing negative values to positive. Adding the result together gets us back to a 1D array. Now, as per the highlighted row above, the number that falls just before the string that we’re after is the only line that has 1 in it. So while Sajan might have ditched sorcery for sword-play, there’s still yet an element of the dark arts in his repertoire. 7. He secures his sharp scalpel for some exploratory surgery on our string: Specifically, he uses the MATCH function to see what position that ’1′ occurs at, and then adds 1. =MATCH(1,{0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0},)+1 =13 Quick aside: I never knew until another competitor in the challenge – Elias – posted a formula in the actual formula challenge thread that if the third argument of MATCH is a comma followed by nothing else, Excel interprets this the same as if that third argument was FALSE or Zero – that is, Excel is looking for an exact match, not an approximate one. So that’s a handy way to shorten a formula if you’re answering a challenge (although I’d actively put the FALSE in in a real-world situation). So with a blank comma as the 3rd argument, all these are equivalent: =MATCH(3,{1,2,4,5},) =MATCH(3,{1,2,4,5},FALSE) =MATCH(3,{1,2,4,5},0) = #N/A And without the comma, these are equivalent: =MATCH(3,{1,2,4,5}) = MATCH(3,{1,2,4,5},TRUE) = MATCH(3,{1,2,4,5},1) = 2 Okay, back to the fight! 8. He culminates all this cutting with a callous coup de grâce to the condemned creature: =MID(A1,13,6^6) =Australia41 That 6^6 bit is just a short way to write a long number. We need a number in that argument that’s long enough so that all possible substring lengths are covered. Sajan could use LEN(A1), but that would take 7 characters, whereas 6^6 is only three characters. The maximum amount of characters that Excel 2007 or later will let you put in a string is 32,767 so given that 6^6 = 46,656 this will be more than enough. Huzzah! How’s that for swordsmanship, eh? Hardly a fair fight…our poor string never really stood a chance against our mighty Samurai. Well, that wraps up the joust for today. But stay tuned…there’s two super-short excellent approaches that have been proposed since this series of posts began, that definitely have to be seen to be believed. I’ll cover them off in a future post. About the Author. Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this: =NOT(EVEN(PROPER(OR(RIGHT(TODAY()))))) That’s right, pure #VALUE! Find out more at http:www.heavydutydecisions.co.nz

Are you interested in learning Power Pivot?

Hi there, I have a question and an announcement for you. Question: Are you interested in learning Power Pivot? Do you want to connect, analyze and visualize lots of data using Excel? IF(Your_answer="YES", "Read Announcement", "Read Announcement anyway!") Announcement: 2nd Batch of Power Pivot coming on August 1st. In February, this year we had our first batch of Power Pivot class going live. It was a fantastic experience. We had more than 340 professionals who learned how to use Power Pivot to take raw data, find insights and communicate better with Excel. In short, they learned to move left to right (see the image below). After 5 months, we are now open for fresh batch of students. This time there is a twist. INTRODUCING Advanced Power Pivot course too That is right. This time we have a whole new level of awesomeness added to the class. We also have an advanced (I must say, highly advanced) class available for you. And this will be taught by, wait for it… none other than… Rob Collie. That is right. My good friend, teacher, blogger , author and awesome Power Pivot magician, Rob Collie will be teaching us Advanced Power Pivot class. Sounds interesting? If all this sounds exciting, then you will certainly love our online class. I am working on creating the course brochure, lesson plans and other details. We are planning to launch this onAugust 1st (Thursday). Meanwhile, I have prepared a few introduction videos explaining what Power Pivot is, how DAX works etc. You can watch these and hear from us about the course by joining our email list. Just fill up the form below or click here. Thank you Thank you so much for your support to Chandoo.org and your passion to learn more. You motivate me to learn new things and share them with you every week.

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 3.)

Welcome back to yet another gripping episode of “When good formulas go GREAT”. We’re working up the singles charts to the number one hit “Formula Challenge 001 – Return everything in string after first block of numbers” By way of a quick refresher, this formula challenge calls for a formula to return a substring from a longer string. Those strings look something like this: String: Required Substring: Monaco7190Australia1484 Australia1484 Liechtenstein42Austria128590 Austria128590 Malta6Belarus78 Belarus78 So far we’ve heard from these crooners: Jeff: Formula Challenge 001 – Part 1 Sajan: Formula Challenge 001 – Part 2 You can download the challenge and full dataset here: Formula Challenge 1.3 Specifically, we need a formula to return only the second country and associated number …in the case of Monaco7190Australia1484 it’s that bit in bold i.e. Australia1484 Part 3 Yesterday, Sajan and his band performed this hot Latin-infused number: =MID(A1,MODE(MMULT((N(ISNUMBER(-MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))={1,0})*(ROW(INDIRECT(“1:”&LEN(A1)))-{0,1}),{1;1}))+1,LEN(A1)) Today, Haseeb shakes his groove thing in return, to the tune of this hard-rock anthem: =MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1,MIN(IFERROR(FIND(ROW($1:$10)-1,A1),”"))+1),”")),LEN(A1)) Rock and roll, baby! Haseeb’s orchestration is really clever: 1. Find the position where every number in the string occurs: Here’s how he does that: =FIND(ROW($1:$10)-1,A1) =FIND({1;2;3;4;5;6;7;8;9;10} -1,A1) =FIND({0;1;2;3;4;5;6;7;8;9} , A1) ={10;8;#VALUE!;#VALUE!;21;#VALUE!;#VALUE!;7;22;9} 2. Remove the errors… =IFERROR({10;8;#VALUE!;#VALUE!;21;#VALUE!;#VALUE!;7;22;9},”") ={10;8;”";”";21;”";”";7;22;9} 3. …so that we can work out which of these numbers occurs first in the string: He does this by taking the MIN of them. Just like the judges on X-Factor, MIN doesn’t handle errors. Which is why he removed them in the last verse. =MIN({10;8;”";”";21;”";”";7;22;9}) =8 So he’s already worked out where that first block of numbers begins – position 8 – meaning he can completely ignore any text that occurs before this position. 4. Get the start of the second block: Now he can search from position 8 for the very next letter. That letter will be the start of the text block we’re after. Ingenious. =SEARCH(CHAR(ROW($65:$90)),A1,8) =SEARCH({“A”;”B”;”C”;”D”;”E”;”F”;”G”;”H”;”I”;”J”;”K”;”L”;”M”;”N”;”O”;”P”;”Q”;”R”;”S”;”T”;”U”;”V”;”W”;”X”;”Y”;”Z”},A1,8) ={11; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 18; #VALUE!; #VALUE!; 17; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 15; 13; 14; 12; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}  5. Get rid of those errors with IFERROR: =IFERROR({11; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 18; #VALUE!; #VALUE!; 17; #VALUE!; #VALUE!;#VALUE!; #VALUE!; #VALUE!; 15; 13; 14; 12; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!},”") ={11;”";”";”";”";”";”";”";18;”";”";17;”";”";”";”";”";15;13;14;12;”";”";”";”";”"} 6. Work out which of these letters occurs first in the string: Again, he does this by taking the MIN of them: =MIN({11;”";”";”";”";”";”";”";18;”";”";17;”";”";”";”";”";15;13;14;12;C”;”";”";”";”"}) =11 7. Split the string from that point forward: =MID(A1,11,LEN(A1)) =Australia1484 Ahh, Australia again. ACDC, anyone? TNT, He’s dynamite TNT and his formulas are tight TNT, He’s an Excel nerd TNT, A shorter formula would be absurd! …or would it ? Tune in same time on Monday and find out. And bring your air guitar this time. About the Author. Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this: =NOT(EVEN(PROPER(OR(RIGHT(TODAY()))))) That’s right, pure #VALUE!

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 2.)

Welcome back. Get a good night’s sleep? Great. Hopefully you had freshly-picked brain-function-enhancing blueberries for breakfast, and a red bull or five, because you’re gonna need it. Why? Because today, my friends, we peek inside the multi-dimensional mind of Sajan the Excel Magician. As you’ll keenly recall from yesterday’s gripping episode of “When good formulas go GREAT”, Sajan claimed Bronze in our inaugural formula challenge at Formula Challenge 001 – Return everything in string after first block of numbers By way of a quick refresher, this formula challenge calls for a formula to return a substring from a longer string. Those strings look something like this: String: Required Substring: Monaco7190Australia1484 Australia1484 Liechtenstein4352Austria8590 Austria8590 Malta1306Belarus7578 Belarus7578 You can download the challenge and full dataset here: Formula Challenge 1.2 (Excel 2007+ only, because the formulas have too many nested formulas for earlier Excel versions to handle) Specifically, we need a formula to return only the second country and associated number …in the case of Monaco7190Australia1484 it’s that bit in bold i.e. Australia1484 Sajan the Magician split those strings with this award-winning beauty: =MID(A1,MODE(MMULT((N(ISNUMBER(-MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))={1,0})*(ROW(INDIRECT(“1:”&LEN(A1)))-{0,1}),{1;1}))+1,LEN(A1)) How does this wizardly wonder work? Let’s find out… 1. Sajan splits apart the source string into it’s characters This is as I did in the last post, albeit using a slightly different formula than I did: =MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1) =MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23},1) ={“M”;”o”;”n”;”a”;”c”;”o”;”7″;”1″;”9″;”0″;”A”;”u”;”s”;”t”;”r”;”a”;”l”;”i”;”a”;”1″;”4″;”8″;”4″} 2. Then he adds zero to the array This coerces any numbers stored as text into numbers, while causing the text bits to throw errors ={“M”;”o”;”n”;”a”;”c”;”o”;”7″;”1″;”9″;”0″;”A”;”u”;”s”;”t”;”r”;”a”;”l”;”i”;”a”;”1″;”4″;”8″;”4″} +0 ={#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7;1;9;0; #VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;4;8;4} 3. Wrap an ISNUMBER around this unsightly spawn of Satan, This turns numbers to True, and turns those hideously unnatural errors of nature to better-behaved booleans:=ISNUMBER({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7;1;9;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;4;8;4}) ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE} 4. Turn this array into Ones and Zeros, and checks if it equals either One or Zero. I know…me too. But let’s give him the benefit of the doubt for now, and see what rabbit hole he’s leading us down…we might yet pop out of a magicians’ hat: =N({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE})={1,0} ={0;0;0;0;0;0;1;1;1;1;0;0;0;0;0;0;0;0;0;1;1;1;1}={1,0} ={FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE} What this has done in effect is create a 2D array – with one column of the array being the inverse of the other. So one column records whether something is a number, and the other records whether something isn’t a number. I know…me too. But maybe it would help if we saw what this would look like if entered over an Excel range, with our original string down the side so we can try and work out what spell this Wizard is whipping together:  5. He then creates a second 2d array with this bit: =(ROW(INDIRECT(“1:”&LEN(A16)))-{0,1}) ={1,0;2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9;11,10;12,11;13,12;14,13;15,14;16,15;17,16;18,17;19,18;20,19;21,20;22,21;23,22} …which if we were to enter over an Excel range would look like this: 6. He then throws this array into his wizard’s cauldron together with the array in 5 – along with generous portions of Eye of newt, toe of frog, wool of bat and tongue of dog. (This simply means Multiply the two previous Arrays) This gives us the following: ={FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE}*{1,0;2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9;11,10;12,11;13,12;14,13;15,14;16,15;17,16;18,17;19,18;20,19;21,20;22,21;23,22} ={0,0;0,1;0,2;0,3;0,4;0,5;7,0;8,0;9,0;10,0;0,10;0,11;0,12;0,13;0,14;0,15;0,16;0,17;0,18;20,0;21,0;22,0;23,0} …which again would look like this if entered over an Excel range (with our original string down the side by way of reference): Well mess up my hair, and call me Einstein. Because now I see what all that was about. The only time that the same number in that first array also appears in that second array is that magical place where the first block of numbers ends, and the second block of letters begins. That is some serious sorcery! 7. Multiply the arrays Next he conjures this 2D array into 1D, with the help of MMULT. Microsoft tells us that MMULT “…returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.” If that’s got you scratching your head, then try this alternate explanation: The M in MMULT stands for Magic.   The way Sajan is using MMULT is to basically add across each row to find out what the total is for each row. Or another way of thinking about it is that because there’s ether a zero or a number in each row, he’s zipping up these two lists into one to just get the numbers and to ignore the zeros: =MMULT({0,0;0,1;0,2;0,3;0,4;0,5;7,0;8,0;9,0;10,0;0,10;0,11;0,12;0,13;0,14;0,15;0,16;0,17;0,18;20,0;21,0;22,0;23,0},{1;1}) = {0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23} 8. Work out the most common number in that array and add one to it Now he’s got the start position of our desired substring. =MODE({0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23})+1 =11 9. Lastly, he uses this trickery to tease out our answer string =MID(A16,MODE({0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23})+1,LEN(A16)) =MID(A16,11,LEN(A16)) =Australia1484 Austrailia1484. Crikey dick. Strewth, mate! That bonza beaut has more bounce than a Boomer! Makes me feel a couple of tinnies short of a slab. And that’s the formula that got the lowly Bronze! Tune in tomorrow, and we’ll see what the Silver-medal winner, Haseeb,has to offer. If you dare….. About the Author. Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this: =NOT(EVEN(PROPER(OR(RIGHT(TODAY()))))) That’s right, pure #VALUE!

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 1.)

The Formula Challenge Series: Where good formulas go GREAT: Formula Challenge 001 – Return everything from a string after first block of numbers Introduction to the Formula Challenge series: Formulas are like the DNA of Excel…you can solve some very complicated evolutionary challenges by stringing together a few simple base-pairs in the right order. Over at http://chandoo.org/forums/forum/excel-challenges you’ll find a whole bunch of tricky formula challenges posted by a group of excel nerds (me included) trying to out-nerd each other with formula nerdiness. Consequently, these formula challenges are designed to get participants thinking creatively about how to solve a tricky problem using formulas only. (Oh, and they might be designed to get a whole bunch of nerds to do my tricky work assignments for me too.) [Psst….so as to not hurt any feelings, I’m obliged to point out under the UN’s declaration of Super-Human Rights that one person who posted a response at this challenge is not an Excel Nerd, thus allowing everybody who posted to assume it was them.] [Psst (again)…of course, in actual fact that lone non-nerd was in fact moi - but don’t tell them that…it will spoil their delusions of normality  ] Putting together a formula to tackle these challenges shows you have a great mastery of excel. Putting together a formula that tackles the challenge and is also shorter than anyone else’s is something more akin to standing on the gold podium at the Olympics and shouting “In yer face, LOSERS” down to the silver and bronze teams while you strut your best Jagger strut to the Windows 95 launch song. http://www.youtube.com/watch?v=5VPFKnBYOSI (Yes, it’s actually that satisfying to a true Excel nerd. Or so I’m told.) Anyway….I’ve learned a lot from looking at how different people tackle these, and my formula toolkit is much bigger as a result. To help you increase your toolkit, today we’re going to take a look at the first challenge in this series: Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 1). Without further ado, here’s the details of the first challenge: Split the strings in Column A with a formula that only returns the substring that you see in column B. So for instance, if the string is Monaco7190Australia1484 then we want everything to the right of that first block of numbers i.e. Australia1484. Your formula should handle any length of string, and any length of numbers – not just the lengths shown above/below You should not use any helper cells, intermediate formulas or named ranges, or VBA Those strings look something like this: String: Required Substring: Monaco7190Australia1484 Australia1484 Liechtenstein4352Austria8590 Austria8590 Malta1306Belarus7578 Belarus7578 You can download the challenge and full dataset here: Formula Challenge 1.1 The bit that’s required to be returned is the Blue Bold bit i.e. the block of text and numbers after the first block of text and numbers, which in the case of Monaco7190Australia1484 is Australia1484. What’s tricky about this challenge is that we have a block of text, followed by a block of numbers, followed by the block of text/number that we actually want to extract. So it’s hard to pinpoint the location of that 2nd block of text/numbers given there’s a preceding block of text/numbers. Despite having a head start of several weeks on this challenge (I first conceived of the approach when answering a post a weeks before posting the problem as a challenge) and despite (or perhaps because of) taking copies amounts of performance-enhancing drugs, I didn’t even make it onto that Olympic podium. Instead, I arrived huffing and puffing at the finish line with this unwieldy beast: =MID(A1,1+MATCH(1,(CODE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1))<58)*(CODE(MID(A1&”a”,ROW(A$2:INDEX(A:A,LEN(A1)+1)),1))>57),0),LEN(A1)) The approach I took was to try to find the point at which we have a number immediately followed by a letter. For instance, the zero and letter A from Monaco7190Australia1484. To do this: 1. I dynamically break the string apart into one-digit bites with this: = (MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1) Excel returns: ={“M”;”o”;”n”;”a”;”c”;”o”;”7″;”1″;”9″;”0″;”A”;”u”;”s”;”t”;”r”;”a”;”l”;”i”;”a”;”1″;”4″;”8″;”4″} 2. Wrap a code function around that which converts these to the specific number that Excel uses to represent each number/letter: =CODE({“M”;”o”;”n”;”a”;”c”;”o”;”7″;”1″;”9″;”0″;”A”;”u”;”s”;”t”;”r”;”a”;”l”;”i”;”a”;”1″;”4″;”8″;”4″}) Excel returns: ={77;111;110;97;99;111;55;49;57;48;65;117;115;116;114;97;108;105;97;49;52;56;52} 3. Work out which of those codes represents numbers. ie: All numbers fall before character code 58 ={77;111;110;97;99;111;55;49;57;48;65;117;115;116;114;97;108;105;97;49;52;56;52}<58 Excel returns: ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE} 4. Repeat steps 1 to 3 but this time I check for the occurrence of letters. Furthermore I make a change so that the resulting array is offset by one position, by changing the ROW(A$1… bit in 1 to ROW(A$2… : =CODE(MID(A1&”a”,ROW(A$2:INDEX(A:A,LEN(A1)+1)),1))>57 Excel returns: ={TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE} 5. Multiply these two arrays together This converts these TRUE and FALSE numbers to Ones and Zeros. Furthermore, because that 2nd array is offset by one, this gives me the position where a letter and a number coincide. (Which – given that 2nd array has been offset – is equivalent to identifying the occurrence of where a number is immediately followed by a letter: ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE} * {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE} Excel returns: ={0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1} 6. All I need to do then is to identify where that first 1 falls in this array: =MATCH(1,{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1},0) Excel returns: =10 7. …and then return all of the original string to the right of that position: =MID(A1,1+10,LEN(A1)) Excel returns: =Australia1484 Summary Neat, eh? Well, I thought so. In fact, I thought that would be a sure-fire winner of the challenge. But that didn’t even get me the Bronze. Rather, that honour belongs to Sajan, who scooped up the consolation prize with this little beauty: =MID(A1,MODE(MMULT((N(ISNUMBER(-MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))={1,0})*(ROW(INDIRECT(“1:”&LEN(A1)))-{0,1}),{1;1}))+1,LEN(A1)) …which requires you to pre-emptively take a whole bottle aspirin AND get a good nights’ sleep before trying to wrap your head around it. So go take that aspirin, take your pens out of your pocket, and hit the pillow, and I’ll see you back here this time tomorrowfor some Einsteinium formula relativity as we tackle this gem of an approach in Part 2. About the Author. Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this: =NOT(EVEN(PROPER(OR(RIGHT(TODAY()))))) That’s right, pure #VALUE! Find out more at http://www.heavydutydecisions.co.nz Comments from Hui: Jeff has been a valuable contributor at Excel Hero Academy during the past two years and recently started posting at theChandoo.org Forums This is Jeff’s First post at Chandoo.org and I am sure you will welcome his verbose narrative to these interesting Formula Challenges

Never use simple numbers in your dashboards (bonus tip: how to fix default conditional formatting)

Pop quiz: What is wrong with below report? At first glance, it looks alright. But if you observe closely, you realize that it is not telling the entire story. Just looking at regional sales numbers, you have not much clue what is going on with them. So how to improve it? 1. Add context In order to know whether a number like $120,000 sales in South is good or bad, you need to provide some context. For example, if you include previous month sales figures, suddenly $120k is comparable to some other number. This tells a better story than a simple number alone. You can also try these, Target values Same month last year values YTD, QTD values 2. Add % Change When you have 2 numbers like $120k and $110k in a report, anyone looking at them are going to mentally calculate the % change from last month to this month. This is easy for numbers like 120 and 110, but if your numbers are like 36,450 and  43,150 then calculating % change values will take time. Why force your audience to do this mental math? Instead show these %s on the report. 3. Highlight bad numbers Another way to enhance your report is to highlight poorly performing regions. Since each region is different, comparing sales of one with another is not good. But you can compare % change (from previous month / same month last year / targets etc.) and highlight poorly performing regions. This can be done with conditional formatting. So lets go ahead and do it for our report above. 3.1 Add conditional formatting Just select the %change column, go to conditional formatting > icon sets > and choose an arrow icon set that you fancy. 3.2 The default formatting kinda sucks We are not done yet. If you look at the default icon formatting, it looks in-accurate. We are seeing red colored, down-ward arrows even when there is a positive change. And, when the % change is negative, we no longer need minus sign (-) because it will be indicated by down arrow. 3.3 Fix the conditional formatting icons Select the cells again, go to home > conditional formatting > manage rules. Select the rule and edit it (you can double click on the rule to edit). Change the rule type as shown below. 3.4 Remove the minus sign Select the %change column once again, go to format cells (ctrl+1) and set the custom formatting code 0%;0% This will make sure that even when the percentage is negative, Excel will not show the sign (minus symbol). Related: More on custom cell formatting in Excel. So there you go. A regional sales report that tells better story. Key ideas to keep in mind In your reports, try to provide as much context as possible. This can be done by providing comparisons including additional statistics (sum, count, median etc.) indicating the time frame of the report highlighting bad numbers or areas that require attention giving user a choice to change report criteria (interactive features). Do you follow these principles when making reports or dashboards? I try to observe these ideas in all my dashboards. What about you? Are you using simple numbers in your dashboards? Go ahead and tell us how you are making your dashboards better, in comments. Analyze data and make reports / dashboards often? If your job involves data analysis, reporting & dashboards, then you will love our Excel School program. In this online course, you will learn how to use Excel to analyze data with formulas & pivot tables, highlight important stuff, create stunning charts & tables, make them interactive and put everything together to weave an informative dashboard & more. Please click here to know more about Excel School program and join us.