Tuesday, 7 October 2014

AET Data Checker

At the moment, I am making lots of reports from raw data. Making sure my calculations are correct is downright tedious. Hmmm...

Here's my latest toy. I say "toy" loosely, not just because I'm a screw loose myself, but because I actually use it at work. Have done so since about 3 o'clock this afternoon and it it's still going strong.

Our company often deals with questionnaires for educational institutions, often with scoring being 1 - 5 or A - E etc, etc. Also sometimes students or staff leave questions unanswered, and this is part of the reports too.

Count Example 1

As you can see I can check several values at once. By the way, <> looks at all cells with a value, = looks at all cells without a value (blank cells) and <>= looks at all cells, as long as they meet the criteria at the top.

And I can change the column I'm checking simply by editing the Look At Column field and clicking the Calculate button. Very, very fast.

Sum Example 1

By changing the Check Type to Sum, I can get the totals. Note how it can do things like calculate all numbers >=4.

Average Example

Here I show the results using optional formatting of 0.0.

Count Example 2

You could also use the form for other things like getting a tally of sales. Note how 20000, 20,000 and $20,000 are calculated with the same results.

Sum Example 2

If calculating totals, why not making things easier by adding a suitable format? ($#,##0)


You can download it here. (Excel 2007 or later only)

See you next time.

1 comment :

  1. Detailed and well explained. Good overview. Thanks for sharing.