Magic Squares in Excel, Part II

Back in 2004, I documented a method for creating magic squares in Excel. This method was passed along to me by my friend and former co-worker Dave Strong. Dave's method worked great for numbers like 34, 38, 42, etc. However, if you used Dave's method to produce a magic square for 35, you would have decimals in each square.

Recently, MrExcel reader Ray Battersby found that web page and figured there had to be a better way. Ray sent in a modification to Dave's method - he found the four secret cells to which you could add a 1 to change the magic square for 34 into the magic square for 35. This method will work for any integer above 30.

Scroll down on http://www.mrexcel.com/tip069.shtml to review Ray's method.

Thanks to Ray for this information!

Dual Sided Histogram

A histogram is a special type of bar chart where all of the bars touch each other. On today's episode of Call for Help, I show a trick from Gerry Verschuuren's book, Excel for Scientists.

If your dataset includes data for both boys and girls, make one of the datasets be negative to have two histograms plotted on the same chart, with each histogram pointing the opposite direction.

Check out http://www.mrexcel.com/tip115.shtml for details.

Discover New Excel Sites with Del.icio.us

del.icio.us is a new concept in websites - it is a social bookmarking site. The idea is simple, you post your favorite sites, and then you can discover other sites that are visited by people who like the same sites as you. I tried it out last week and I can see the benefits in discovering new Excel sites or even for developing a comprehensive list of cool Excel sites. Check out http://www.mrexcel.com/tip113.shtml for complete details on setting up a del.icio.us account.

Track Progress Towards a Goal in Excel

Episode 304 of Call for Help airs on a timely date. Now that we are past the holiday, it is time to start thinking about those new year resolutions. Want to run 50 km in January? Use this cool chart to track your progress towards a goal.

The chart includes a trendline that will predict if you will meet the goal if you keep working at the present rate. I've used charts like this to motivate me to finish books (tracking pages written each day) or to finish a project (tracking lines of code written each day).

Tip 111 contains the complete steps and show notes.

test

Technorati Profile

Pivot Table Cursor Meanings

In today's episode of Call for Help, I slow down a bit to show you the various mouse cursors that you will see when creating a pivot table. It is very subtle, but the mouse cursor changes as you drag new fields to various portions of the table. By watching the mouse cursor, you can know if you are about to drop a field in the row area or in the data area, etc.

Tip 112 contains the show notes with more details.