3

So I've created a spreadsheet program that will import a selected bitmap and use Excel as a canvas to display it.  Each cell is a pixel.  Here's a picture of president Obama in 8-bit glory:

And here's my current March Madness bracket in stunning 24-bit:

You can download the file below, and instructions are included on the Instructions tab.  Don't worry, it's not hard to use.  If you can use MSPAINT, you'll be making your very own Excel art in no time!

For now, the code is locked, but I might make it available later after I'm sure it's error free. Speaking of which, there are a few problems you may (but hopefully won't) run into:

1.  The picture shows up discolored and slanted.  While I'm fairly certain that I've fixed this issue, I admit that I am by no means perfect.  When bitmaps are saved, they are "padded" in the file to make their height evenly divisible by four.  The program should compensate for this padding, but if it doesn't -- again, I'm fairly certain this issue is resolved -- just resize the bitmap it to the nearest number evenly divisible by four.  (That number is at most three digits away.)

2.  Too many Different Cell Formats error.  Sometimes you'll get this annoying popup.  If that happens, try zooming in on the canvas screen first and then reuploading your file.  If that doesn't work, either crop the original image or resize to a smaller one.  Or, if you are uploading a 24-bit bitmap, consider resaving as an 8-bit and then reuploading.

If you find other issues, please, let me know.  It would be great help.

Also, this is best used with Excel 2007 and above.  It will work in 2003, but remember that 2003 has a column size limitation, which may cause in an error for files that are too large.  If you are concerned, smaller is better: resize or choose to save in the 8-bit format.  When you are done, remember to save as in the Excel 2007 file type (check to see if you are in compatibility mode) so that you get all the columns required to display your most productive spreadsheet, ever.

I'm releasing this now because I've been sitting on it for way, way too long.  I was hoping I'd come up with some real, professional use for it, but I haven't thought of anything yet.  For a moment, I was trying to apply a Hough Transform to the bitmaps to see if I could take images and turn them into cool free form shape, but this proved too taxing for Excel.  I also made an attempt at Eigenfaces which ended ridiculously.  So if anyone out there has some good ideas, please email me and we'll work together.

Lastly, if you like what you see and choose to host a spreadsheet made with this program (or screen capture of one) on your blog or website, I'd be so grateful if you mentioned this blog!  Have fun.

File
Bitmap to Excel.xlsm

Author: Unknown Labels: Advanced Excel, Bitmaps

3

We've all had some experience with Excel's automatic numbering.  For example, if you have a simple list and you would like to add consecutive numbers to a column next to your data, you'd simply select the cells containing your first two numbers and then drag accordingly.  Take a look at the sheet below to see an example.

But real-world lists aren't always so simple.  And for certain data, a simple consecutive list just won't do.  Sometimes we receive spreadsheets that have some type of intrinsic grouping but no unique group identifier. The challenge is that we would really like to apply Pivot Tables to this data, but we can't do anything until there exists some type of group identifier.

Problem #1: Items grouped without collation
Take a look at the datasheet below from my nonexistent accounting information system.

Because I'm smart(ish), I know that a new group begins every third row of data.  The first three rows of data (Excel rows 2,3, and 4) represent store 1, the next three store 2, and so forth.  I would like to add an additional group_id column such that each grouping is numbered consecutively.  Something like this:

How?  I use this formula, 

=INT((ROW()-2)/3) + 1

then fill downward.  This works by applying integer division in the amount of each item in a group to the current row. So, for row 3, we have 

=INT((ROW()-2)/3) -> =INT((3-2)/3) - > =INT(1/3) -> = INT(0.33) = 0.  

(If it's been a while since your last algebra class, just think of it as dividing and then "rounding down" the result.)

I add one ("+ 1") at the end of the formula so that my grouping doesn't start at zero.  That's optional.

Notice though, that I subtract 2 because my data starts on row 2.  If I had started on row 1, I would likewise subtract 1; for row 3, subtract 3.  Here's a cheat sheet:

For Items Grouped Without Collation

=INT((ROW()-First row of data on spreadsheet) / # Items per Group)[+1] optional  

Problem #2: Items grouped with collation


In the following spreadsheet, we've culled information from many different spreadsheets maintained by many different people.

The problem is that each spreadsheet administrator used a different naming convention for the same account (see the highlighted accounts).  And take a look at the final Retained Earnings and note that it is labeled "  RE."  Those extra spaces can creep into the spreadsheet and easily go unnoticed.  What a nightmare.

But wait, our list has some semblance of order: accounts can be grouped every sixth row. We just need to group each item, one through five, until the end of the list.  Like so,

How?  With this formula,

=MOD(ROW() - 2,5) + 1

This should look familiar to the one above.  But instead of using integer division, we're now using modulo division; that is, we're interested in the remainder.  Take row 3

=MOD(ROW()-2, 5)+1 ->  =MOD(3-2, 5)+1  - >  =MOD(1, 5)+1  -> = 1+1 =2.

Here's the cheat sheet:

For Items Grouped With Collation

=MOD((ROW()-First row of data on spreadsheet,# Items per Group)[+1] optional  

Final Thoughts

1.  Once you've created your new group_id column, it's a good idea to select your new work, copy, and then paste as values.  If the groupings aren't going to change later, there's no reason to keep it as a formula.  Remember, fewer formulas means better Excel performance -- especially if you plan to use a Pivot Table later.

2.  If you're not into the numbering scheme, create a lookup table that maps the numbers to a proper name.  Create another column at the front of your data and use a lookup method, like Index, to map the correct names.  Then do a copy/paste values.

3. If you have a list scheme you use quite often, you can actually save it as a custom fill series and then use it later.

Remember:

For Items Grouped Without Collation

=INT((ROW()-First row of data on spreadsheet) / # Items per Group)[+1] optional  

For Items Grouped With Collation

=MOD((ROW()-First row of data on spreadsheet,# Items per Group)[+1] optional  

Author: Unknown Labels: Data analysis, Pivot Tables

5

Have you had a list of names but found your cell width too small to show every name?  Take a look at this:

I had my nonexistent secretary print out this fake itinerary for me.  Nothing lines up neatly.  Look how some of my events overlap the cell boundary between columns B and C.  My 2012 End-of-world Steering Committee meeting on February 1st, 2013 goes long and is then cut-off by the date!

To fix this, you could have Excel automatically size Column B, but that's a bit unpredictable to have on a dashboard (what if other content is sized off the screen?).  We'll just have to accept that we can't show everything, but we can make it look neater.  We'll employ some type of text truncation method similar to what Microsoft uses to display the names of files in Windows Explorer: if the text to be displayed is greater than a certain character length, simply truncate and add a "...".

The "Original Recipe" Method

First thing we'll need to do is figure out how many characters we'll want to display before truncation.  There's really no way to do this without trial and error. But, to make life easier, we can use =LEN("Your Text Here") to return the length of your text; or, we can use something like  =LEFT(B3, 4) to return the first four characters of text in cell B3.  Keep trying until you find a good character length.  

Hopefully, you got a good handle on using LEN and LEFT above because we'll be using them from here on out.  Also, I'm going to use 25 characters as my desired character length before truncation.  Below I use  LEFT to pull the first 25 characters from each event name.

Now we'll need to know which names to add the "..." to.  Following ExcelHero's discussion on using IFs, we'll avoid using one here; not to worry, we don't really need it.

As it turns out, LEFT can take a zero value character length -- it just returns nothing.  So to have Excel automatically return what we want, we'll do this:

=LEFT("…",LEN(B3)>25)

Take a look at that second argument on the right.  It takes the length of our event name and tests if it's greater than our cutoff length of 25.  If it's less than 25, Excel will return a zero and display nothing.  If it's greater than the cutoff, Excel will return a 1 and display the first character in our text.  But there's something I found out while writing this tutorial, I expected Excel to only return one period ("."), but instead it returns all three ("...") even if you only put a 1 in on the right.  I'm guessing that the ellipses are treated as one character; however, I'm dubious that this works in every case on every computer, so to have your formula return three characters or zero, you'll simply change it to:

=LEFT("…",(LEN(B3)>25) * 3)

Now you'll need to concatenate both strings together using the concatenation operator, &.

Finally, as you can see in cell, H7, there is a space between the event name and the ellipses.  This is because the last character before the cutoff was a space.  To fix this, surround the first string formula with the TRIM function; this will automatically delete any leading or ending spaces.  

Putting it all Together

To finish, we'll combine all our steps into one formula, like so:

=TRIM(LEFT(B3,25)) & LEFT("…",LEN(B3)>25)

To get:

...mmmmmmuch better!

Download my example: Spillover Design Example.xlsx

Author: Unknown Labels: Excel Tricks, No VBA, Strings

0

In the last post, A Dynamic Dashboard for Project Durations and Costs in Excel, we talked about using VLOOKUP as means to map references to their costs.  At the end of the post, I admitted that I didn't really like the solution.  Specifically, I didn't like how the user had to type in each reference manually and then go back and ensure their numbers were correct (for example, if execution is nine months long, the user would need to ensure they type the number 2, nine times).  In this post, we'll talk about how to automate this process.  When we're finished, you'll only need to type the number 9 into a box and the rest will update automatically.  

The Advanced Method

Step 1: Use the REPT formula

The REPT() formula (short for "Repeat") allows you to repeat a character a certain amount of times.  For example, if A1 has the formula =REPT("A", 6), then A1 will display the value "AAAAAA."  Now think about how that might be useful here.  We could simply input the number "6" for IT Services in the Execution phase and Excel will generate a reference string "222222."

Consider below:

In this case, =REPT(C$11$, C3) is telling Excel to repeat the value 1 (our reference to planning) a total of two times.  In other words,  =REPT(C$11$, C3) -> =REPT("1", 2) -> 11. (Remember, "11" is two 1's, not eleven.)

Step 2: Create the reference string

Now concatenate each string reference (using the concatenation operator "&") to form the entire reference string.

  

Step 3: Read from the reference string using the MID formula into your References Table

The MID formula allows you to take a string and read its characters starting from a specific position to a certain character length.  For example, =MID("Jordan",2,2) returns "or" since the first 2 tells Excel pull from the second character in "Jordan" and the second two tells Excel the amount of characters to return.  For our purposes, we'll only need to return one month at a time.  And since our reference table incidentally uses numbers for months, we'll reuse those numbers to tell the MID formula our desired character position.  When you supply a character position greater than the character length of the given text string, MID simply returns nothing.  

Above, we tell Excel to take the corresponding reference string for IT Services and use Month 1 to pull from position one in the reference string. Fill in the information going across, then down.  

Consider what we've done so far.  Now when you change the information in your Months in Step table, the reference strings change, which makes updates to the Reference Table automatically!

  

Step 4: Convert references to numbers in your Values Table

We have a small problem. Our Reference Table may be displaying the correct information, but it's actually displaying the information as text and not a number (this is because we used String formulas above, which work on and return text).  If we try to add the the numbers for the IT Services row in our Reference Table above, the result will actually be zero since, despite appearances, no numbers actually exist in the row.  Luckily, the fix is simple.  We'll use the VALUE() formula to convert the strings back to numbers.       

Step 5: Ditch VLOOKUP.  We don't need it.

VLOOKUP is best used to look up strings, not numeric values.  I've updated the look up table below.  Take note that each phase is actually intrinsically encoded: Planning is already in row 1; Execution in row 2; and Maturity in row 3.

In this case, we need only use the INDEX() formula.  The INDEX formula allows us to pull from a specific row and/or column in specified range.  For example, we could take the last column above and use the numbers in our Reference Table to identify the desired row.  Take a look below:

The Values Table uses 1 in the References Table to pull from the first row of our lookup. 

Step 6: Use IFERROR for months with zero values

Another small problem.  The INDEX formula above is reading in blank values and returning errors!  You'll need to tell Excel to return $0 for those months.  To do so, you'll use the IFERROR formula.  IFERROR works by attempting to execute a given formula; if the formula returns an error, IFERROR will direct Excel to return another value instead of an error.

Take a look: 

Step 7: Sum the Values Table, then graph

Summary
Hopefully that wasn't too bad.  I wanted to show how many different formulas can interact to create an automated dashboard.  Of course, you could tighten some formulas up.  For example, instead of using VALUES, you could simply use the "--".  And, you could even do away with the IFERROR by retooling your lookup table.  I'll leave that up to you.  


Something to try...
Use the reference table and conditional formatting to create a gannt chart!




We're still missing some desired functionality.  Wouldn't it be neat to be able to set the date when each project begins?  We'll talking about that in Part 3.  Don't miss the riveting, final conclusion!


Download the example file: Dynamic Duration Example.xlsx

Author: Unknown Labels: Dashboards, Dynamic Charts, No VBA, Project Management

3

Make sure to checkout A Dynamic Dashboard for Project Durations and Costs in Excel (part 2) for the example file!

Let’s say you are the manager of a portfolio of projects at your company.  While each project under your aegis is different, they have a few things in common; specifically – and for the sake of our example – they all go through (or, are currently in) three different phases, and each phase has a different cost per month, but the cost doesn’t differ between each project.  In our example, the phases are:

Phase Name Cost of each stage per month
1. Planning $250/month
2. Execution $500/month
3. Maturity $125/month

The idea is to capture both the cost in any given month and the overall cost over the life of the project.  For example, you have an IT Upgrades project in your portfolio.  You think that you will spend two months in Planning, nine months in Execution, and one month in Maturity after which the project will be closed.  Thus, the overall cost of your project is expected to be,

Planning @ two months: 2 months * $250 $/month  = $500
Execution @ nine months: 9 months * $500 $/month = $4500
Maturity @ one month: 1 month * $125 $/month = $125
Planning + Execution + Maturity =  $500 + $4500 + $125 = $5125

The problem is that each project is different.  One project might spend more time in the first phase than the other.  Moreover, for some projects you have a good idea of how long they will stay in each phase, and for some you are a bit less sure.  Boy, you say, wouldn't it be nice to change how long a project stays in each phase to compare costs.  Wouldn't it be nice to see it on a chart?  Let's see what we can do.

Using a VLOOKUP might seem like an obvious choice for our solution.  Here's how to do it.   

The Dynamic VLOOKUP Method

Step 1: Create the VLOOKUP table
Since you know there are three projects, you could encode ids into a lookup table numbers as follows.

Step 2: Create a Reference Table
The Reference Table is a time-series table that holds the numerical references to your VLOOKUP table.  If IT Services has two months of Planning, then the Reference Table will show two 1's in the first two months, respectively.

Step 3: Create a Values Table to map each reference to its monthly cost using VLOOKUP
Now, you'll make another table that is essentially a mirror image of your Reference Table. This table, however, maps each reference to the correct cost.  So, for Month 1 in the Values Table, VLOOKUP will use the corresponding reference (1) in the Reference Table to indicate the project is in the Planning phase and the associated cost is $250.

Step 4: Sum the Values Table, then graph

Summary
Our dynamic VLOOKUP() method works: the manager (that's us) can replace the values in the Reference Table to update how long a project stays in each phase.  We would simply repeat numbers in the Reference Table in the amount of months a project is in a particular phase.  Our Values Table automatically updates based on each change.



So here's the thing.  I don't like this method at all.  For one, we need a VLOOKUP in for every month, for every program.  Our example only displayed a few projects in a small time frame, but the real world might have many projects over many years!  That's a real problem because VLOOKUPs can become computationally expensive as our spreadsheet grows.

And there's another problem, too.  The manager – that's us, remember – must hand-jam the references in for every month. What if there are many years to account for?  We could Copy/Paste to make life easier, but this method is so very, very error-prone.  There should be a way for us to simply enter the months a project is in a given phase and have Excel automatically generate all the required reference information.

Let's talk about how to do that in the next post.

If you're having trouble with the work above, take some time to see if you can recreate your own version in Excel.  Even if you have followed everything so far, consider the VLOOKUP method and the importance of using numeric references.  They will play a very key role in part 2. 


-----


Now Available: A Dynamic Dashboard for Project Durations and Costs in Excel (part 2)

Author: Unknown Labels: Dashboards, Dynamic Charts, No VBA, Project Management

1

Most importantly, use your words
An ideal Excel filename should be two or three succinct words and contain few numbers. Current operating systems no longer constrain filename character length, so there is neither excuse for nor cleverness in using shorthand. Capitalize each word as you would a document title.    

Only abbreviate proper nouns
If your file is an example to someone, it should have the full word “Example” not “ex” in its title. If your Excel dashboard is the second version of the “Cost Analysis and Reporting System” you may abbreviate your filename to “CARS v2.xlsx,” but a VBA Chart Tutorial should never be named “VB ChrtTut.xlsm.”

Always connect words with a space and nothing else
The name of your file is not a programming variable or engineering quantity. The words in your filename should not be connected with underscores (“_”) nor dashes (“-“).

Use clear dates, but don’t include dates in every filename
Unless your file is a report that comes out on a specific, periodic schedule, there’s likely not a good reason to put today’s date in your filename. If you must put a date in your file, place the date at the beginning, left-hand side of the filename so it appears first. This ensures the date is not cut off when viewed in a file explorer. Dated files are likely to be stored with similar files in the same folder, so cutting off the last bit of each filename on the right is less harmful than cutting off the date.

If the workbook is a monthly report, the name should by the three character month followed by the year, then another space for the filename (e.g. “May 2012 Cost Report”). For reports that occur more frequently, a MM-DD-YYYY is preferable for sorting within the file directory.  \However, dates must always be clear; do not write “03-12” as this could mean March 12 or March 2012. Finally, full dates should not be clumped together without a dash (bad: “03122012”).

Numbers are preferable to dates
If you have several iterations of a file, use a numbering system instead of dates. Using dates leads to the horrible practice of adding extra numbers at the end of the filename. For example: “InventoryList 22 Feb 2001_1.xlsx”, “InventoryList 22 Feb 2001_2.xlsx” … etc.  Moreover, using dates and the former practice will not instantly make clear the latest version of your file when viewed in a file directory. However, placing a number at the end of your filename (“Inventory List 1.xlsx”, “Inventory List 2.xlsx”) always will make clear the latest (and first) iteration of the file whether sorted by filename, file type, or date modified when viewed in a directory (these files will always be either first or last). Numbers always should appear as the last character on the right.

Author: Unknown Labels: Excel Standards, Filename