onsdag 17 augusti 2016

Stack Overflow

I have been hanging out on the community Stack Overflow recently. It gives you very good resourses of help as long as you bother to post any well formulated question within the community sub-topics.  lI will try to share my views on the structuring of spreadsheets in the new documentation sections therein. Please follow my attempts of creating some order among all these excel files there!

torsdag 7 juli 2016

Separate data from calculations and immediately boost reusability!

On the common task of doing the same thing over again, like presenting an old diagram or performing an old analysis of any kind on a new set of data; it greatly helps to have data and calculations carefully separated. So, to reusue old charts for new data, how would you do? Just copy the old thing, like a sheet with your carefully designed diagram, and fill in the new data in it! From the brilliant tech and excel blog Peltier Tech.

However, this separation is not at all facilitated by Excel itself, you have to be disciplined enough yourself to enforce it!

tisdag 5 juli 2016

32 bit Excel - stay away from its memory limit!

In the 32-bit version of Excel which most of us still use I guess, there is a limit on the memory you can access at about 2GB, i.e. much less than your computer probably is equipped with. I recommend you to stay away from that limit as your Excel session may not behave that predictably when you come close to it.

I use to look in the "Windows Task Manager" in tab "Processes" in the column Memory "(Private Working Set)". In the row for "EXCEL.EXE *32"; if the Reading is below 1,000,000K I feel OK with it, if it is above 1,500,000 I usually need to rethink how many formulas there has to be and what workbooks that needs to be loded.

Constant values on the other side are much less memory demanding.
Edit: there is a discussion on the 32 bit Excel 2GB memory limit issue here!

onsdag 29 juni 2016

Excel style tip

Excel spreadsheets are often hard to understand and prone to errors. A few things would simplify greatly, I try to follow the list below as often I can even though it is sometimes quicker to do things in an ad-hoc fashion:
  • Use named references to make formulas possible to understand and independent of input moving to other cells
  • Use data tables, because they facilitate the above and automatically keeps track of the number of rows in your data
  • Use pivot tables, because they can perform most of the tasks you would do anyway, but in a stable and standardized fashion
  • When pivot tables do not give you the structure you want for your output, use GETPIVOTDATA to dig out what you need in your desired format from the data you have analysed with the pivot table

The VALUE function and the "+" prefix - a very simple Excel quiz

The Excel manual is not for every function written for formal completeness; when it comes to the at least in my veiw fundamental Value function, it seems rather written from the perspective of a typical user.

Consider this example (which may surprise you): in the three cells A1 to C1 write eg 1, 2 and 3 respectively. In B2 write "=SUM(A1:C1)", which evaluates to 6. Then instead write "=SUM(A1:C1+0)" or" =SUM(VALUE(A1:C1))"  and what do you get?

The thing is of course that the "+0" triggers evaluation of the expression "A1:C1+0" before passing it on to SUM. And the VALUE function does the very same thing, it evalutes its parameter from the perspective of the cell where it stands, exactly as if it was written alone in the cell after a "="-sign. But the Excel manual only speaks of a very special application of this, namely that it: "Converts a text string that represents a number to a number"

EDIT: More elegantly (and perhaps also more general), On Stack Overflow I've been directed to put a plus sign "+" before a variable when passing it to a function to make sure it is read as a scalar. I the above case you would write the more compact "=SUM(+A1:C1)".

onsdag 22 juni 2016

Safer linking to external sheets and workbooks

Linking to external sheets is common but could be risky. Here is how I do it to reduce those risks.

Supposedly, you get a lot of monthly reports (or reports with any other frequency) on Excel sheets. And your task is to compile and consilidate to a master report. So you get someone to cut and paste data from the reports, or you even have to do it yourself.

When the operation grows, you save time and have fewer errors by setting up direct links to those other reports, like for instance =another_report!$B$2. And that will be just fine until the layout of the reports change and your link suddenly points to the wrong cell, with some risk of giving you an unintended, erroneus result without warning.

What to do then?
Well, the reports you receive have numbers on them, and immediately to the left of the numbers there are hopefully some text telling what the number represents, like "Sales" and then to the right the sales figure, let's say "1700". So you could easily create a good name for the cell with the sales figure by using Excel's "Create Name from Selection" command (shortcut Alt-M C in Excel 2010). That command would automatically give you the name "Sales"and you could safely refer to =another_report!Sales. Just run "Create Name from Selection" on the relevant parts of the incoming reports and you will stay safe until the lables chage, like Sales -> Projected Sales. That would then give you a #Ref! error in the cell with =another_report!Sales. However, rather than giving you a wrong number, the #Ref! error would prompt you to take the right action (like looking up the lable for this months sales figure and changing the formula accordingly).

Got it?