Category Archives: Uncategorized

Excel Geekness

Every once in a while I go geek. I don’t apologize for it. It’s been a fixpoint in my life since I was old enough to crawl under the coffee table and push the big buttons on our bulbous and bright red 70s TV set.

The TV finally broke (NOT my fault), but the habit didn’t.

This time around it happened with Excel. I’m not the biggest Excel fan out there, but I needed to make a TCO calculation and basically wanted Excel to do two things for me:

  1. Distribute project costs over multiple years based on project-timeline.
  2. Distribute operational costs over multiple years based on how many years were between each payment.

Geeked out yet?

No?

Lo and behold, all I could find online was that this was impossible. You could do nifty macros, you could color every other row, you could sum every Nth row, but if you had an invoice coming in every 3 years and wanted a generic way to get yearly budgets N years in the future, you were basically s… out of luck.

So I did this:

For distributing project costs over n years:

IF(AND(YEAR($startdate)=$currentyear;YEAR($enddate)=$currentyear);
  $projectcost;
IF(AND(YEAR($startdate)=$currentyear;YEAR($enddate)>$currentyear);
  $projectcost/($enddate-$startdate)*
  (DATE($currentyear+1;1;1)-$startdate);
IF(AND(YEAR($startdate)<$currentyear;YEAR($enddate)>$currentyear);
  $projectcost/($enddate-$startdate)*365;
IF(YEAR($enddate)=$currentyear;
  $projectcost/($enddate-$startdate)*
  ($enddate-DATE($currentyear;1;1));0))))

I break the project cost down to a cost-per-day , and then distribute it over the years based on how many project days there are in each year.

The first IF is to determine if the start and end-date is within the same year, and the next three are to differentiate the first year from a “middle” year and the last year as each of these calculations are slightly different. Pretty straightforward.

For projecting operational costs with n years between them

IF($recurrence<>0;
  ROUNDDOWN((-(MOD(($currentyear-YEAR($startdate));$recurrence))
  +$recurrence)/$recurrence;0)*$cost;)

This one is a little more complex and was the one I couldn’t find anywhere. The first half figures out if the time from the startyear to currentyear divided by the recurrence is a whole number (i.e. that it’s time to pay). If it is, it creates a 1, and then I just multiply that with the cost.

Now I don’t pretend to be an Excel wizard in any sense of the word, so don’t ask me technicalities about why I used one function instead of another. All I had was some very helpful colleagues who were excellent guides into Excel-syntax and once I understood most of how it worked, the rest sort of came on its own .

So if you can use it, feel free to share and use it as much as you like. I’ve attached an example of the whole thing working. Just play around with the input and try adding as many years as you like to see how it works.

Good luck :)

More info:

Is your Smartphone killing your Dads job in IT?

It has been obvious for some time that the revolution of IT is fundamentally changing businesses.

At one end normal people, like you and me, are buying increasing numbers of personal gadgets, bringing them to work, and using them as a central part of our working life. Along with our Gmail, Facebook, and other social services, they are an interface to an online presence that is no longer just a fun time-waster, but an essential part of our social- and knowledge- nervous system.

With our online gadgets we are legion, without them we are merely individuals, and the IT department that used to run your PC for you are finding themselves with an increasing challenge of figuring out, whether to just let loose and allow everybody to bring their own devices, or to implement some heavy-handed Device Management and not allow any data outside their control.

At the other end of the spectrum, the huge mainframes and IT infrastructure that used to power companies are slowly but surely being challenged too. Running IT is fundamentally ruled by the same volume dynamics that we’ve known since the industrial revolution: The bigger the data-center and the more people you service, the better the price-performance.

It used to be that every company, no matter how small, had an IT guy responsible for running servers. Whether it was just one server under a desk or a few in a closet, you needed something people could log into, share files, and work from.

Not so today. Today, smaller companies are increasingly doing what we as individuals have been doing for years: buying all of these services online. Dropbox, Amazon S3, Office 365, etc. are all services where you can get a better service for much lesser cost simply because it is much cheaper to run things like that if you’re doing it for millions of people instead for 5-6 people in a small office.

And as this phenomenon, normally called Cloud computing, is maturing and becoming more and more pervasive, it is moving up in the world. It started with the simpler services, like e-mail and online collaboration, causing even Fortune100 companies and governments to start moving these services into the cloud to get savings, but now even old complex mainframe vendors are starting to offer their systems through the Cloud too because they can do it at lesser cost than even major companies can do on their own.

So where does that leave your Dad’s IT department?

The main job of an IT department used to be running the infrastructure, the servers, and the PCs that everybody uses, but the infrastructure is in the cloud, the servers are soon to follow, and you’ve brought your own device.

In other words, it is foreseeable that IT departments will become merely procurement-experts, buying the services that the company needs without running anything, and that the task of ensuring security will be managed through corporate policies like: “make sure your device is backed up in the corporate cloud, can be remotely wiped, and is encrypted” etc.

So yes, unless your Dad works in a company that is running IT for others, it is in fact likely that your Smartphone is killing off his job, his department, and the entire role that IT departments used to have in companies.

But as with all changes driven by cultural and social progress, it is not something to feel guilty or sad about. It just is, and that is as it should be.

Read More