Microsoft Excel CSE Functions

Posted by batman on Sep 16th, 2010
2010
Sep 16

Have you ever felt that, while the "SUMIF" and "PRODUCTIF" functions in Excel are great, there's just not enough of them? Wouldn't a "MINIF", or "MAXIF" or even an "AVERAGEIF" have been really nice as well? Or even the ability to have a custom "MYCUSTOMIF"?

Then welcome to CSE functions. CSE is an anagram for Control Shift Enter, and is named such because, unlike just typing in your formula and pressing Enter, you need to type it in, and press Control Shift Enter – or else it just won't work. And you'll need to make a note of this, because you probably won't remember this in a few years time.

OK, so what are CSE functions? By definition CSE functions are functions that operate on arrays (they are also known as array formulae). An array can be one-dimensional (single row or column), or two-dimensional (multiple rows or columns). An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and calculate a column or row of subtotals. You can also place a formula in a single cell and calculate a single amount. An array formula that resides in multiple cells is called (logically enough) a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.

CSE functions can be identified by being wrapped with curly braces i.e. "{=MIN(IF($C$2:$C$20=A5,$I$2:$I$20))}", wheras a regular function would be "=SUM(A2:A20)".

I won't attempt to provide a tutorial on CSE's – no point in re-inventing the wheel. There are several really good resources already out there. You can try any of these to get a kick-start:-

 

Powered by Zoundry Raven

Technorati :
Del.icio.us :
Zooomr :
Flickr :

Share and Enjoy:
  • Facebook
  • Twitter
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • LinkedIn
  • MySpace
  • laaik.it
  • Google Buzz
  • Technorati
  • Slashdot
  • Reddit

My PC Software

Posted by batman on Feb 27th, 2010
2010
Feb 27

I have compiled a list of programs and software I use on my home PC. It isn’t intended to represent a survey, or exhaustive study of the “best-of-breed” – rather just stuff that I have found, and that work for me. I have no doubt that there are many, many other programs out there that are better than what I’ve got, but my setup does what I need it to. Hopefully it will help you with yours 🙂

Operating System

While I am a Linux person by nature, it just makes more sense to run Windows XP at home. The kids are always wanting to plug in MP3 players, or watching video clips, playing online games (with flash) – and inevitably on Linux one generally needs to spend some configuration time before it works. With Windows – unfortunately – it often just works.

Web Browser

Ok, this one is a no-brainer. Mozilla’s Firefox wins hands down, simply for the way it consistently outperforms Internet Explorer as far as new features, security and lack of vulnerabilities are concerned. No further discussion is even required.

Password Manager

I use Gorilla Password Manager to maintain and protect all of my passwords. It is freely available, open-source and best of all – cross platform. This means that one common password file can be re-used across Windows, Mac OS X, Linux, Solaris and *BSD operating systems. The “Twofish” encryption algorithm is used to secure your password database, and it is not possible to break into it without having the master password. It allows me to store a multitude of login details and passwords, in a hierarchical structure, and allows me to generate seriously secure passwords for sensitive sites. Good luck tring to crack my password of Gj_)bGF5$,!l:;@#jhsTg*(-+

Backups

I use Corbian Backup to backup my valuable data. It is freely available and does everything I need it to do. I can create several tasks, include files and folders and package them in a zip file in a specified folder or folder. I can schedule them to run automatically, so that perhaps my only responsibility is to backup this Corbian folder to DVD or secondary harddisk as and when I deem it necessary. It is highly customizable and would certainly perform as well as – if not better – than many commercial products on a scale much larger than my home PC.

Anti-Virus

AVG is one of the most widely used free virus protection utilities out there. Although in this case there are many other competitors, and in some cases, or as argued by some people, many of them superior to AVG. Touch wood, no virus has yet caused me damage, and AVG has picked up a few. This is one of those cases where commercial products will actually offer vastly superior protection – it just depends on how much you’re prepared to spend.

Desktop Blogging

I am a big fan of Zoundry Raven for creating, editing and publishing blog articles to several blogs. It has a true WYSIWYG editor that is really simple and straightforward to use. Zoundry supports a whole host of destination blog types – I use WordPress, and it works flawlesly. Admittedly I am not a super-blogger, but it certainly does everything I need it to do, and certainly beats having to edit articles “online” – using one of WordPress (or other) built-in editor. The edit-local and publish model just makes so much more sense than the online-edit model.

File Manager/Transfer Clients

  • FileZilla is a simple but effective ftp client that does everything I need it to. As with many of the software on this page, there are many others that will perform equally, if not better. For me – if it works – it’s good enough.
  • WinSCP is a Windows implementation of the *nix function SCP (Secure Copy). It has an awesome amount of features, and one can do almost anything with it. It allows me to easily communicate with my Linux Ubuntu installation, running as a virtual server
  • FreeCommander is an alternative to Windows Explorer. While Windows Explorer does pretty much everything FreeCommander can, FreeCommander offers its functionality with a different interface, and I just prefer using it. It strongly mirrors the two-panel interface of the old blue Norton Commander, or of late, the *nix Midnight Commander (mc as it is better known). F5 means copy, F8 means delete – I just feel at home using it.

Other

  • JRuler is a handy little screen measuring utility – it offers a ruler calibrated in pixels, mm or cm (if my memory serves me correctly). I use it a lot for web page design, when one wants an indication of how big something must be, without the rigour of “trial-and-error”. Very simple, but effective – it does what it was designed to do.

Virtualization

I use VMWare Server– a free option ideally suited to a home pc. The limitations are by no means limiting, as I could run several virtual servers, can configure all of them and allocate resources to my heart’s content.

VM: Ubuntu – KDE

My distro of choice will always be Debian-based – the “apt” method of installation is seamless and reliable, and makes installations a walk in the park. Updates of packages as well as distribution upgrades are really childsplay – the single provisio is that you need a good internet connection. My personal distro of choice is Ubuntu, or rather Kubuntu (Ubuntu + KDE)

VM: Web Content Filtering

Dans Guardian wins this one hands-down for me – to the point of running a linux server whose sole purpose is to provide this capability to my Windows OS. It is extremely powerful and configurable, although out of the box works just fine. I am quite comfortable letting my children aged under 10 loose on the Internet without supervision, knowing that Uncle Dan is keeping an eye out for them. This is one of my Killer Apps, and nothing else on Windows comes even close.

VM: Source Version Control

Even though CVS is aged and improved on by many products these days, it nonetheless still does everything I need from it, and it does it well. I have no need to upgrade, or look for anything more.

Share and Enjoy:
  • Facebook
  • Twitter
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • LinkedIn
  • MySpace
  • laaik.it
  • Google Buzz
  • Technorati
  • Slashdot
  • Reddit