Software Africa Newsletter - September 2022

Main Rant ~ Online Business Tip ~ Excel Tip ~ Training Tip

Rick's Editorial

Welcome to the Software Africa Newsletter: Subscribed to by the Select, read by the Elite, acted on by the Exceptional.

This month I ask if you can help with my saga with the dysfunctional Master of the High Court.

Should a company fire the programmer as soon as the program works, before they start to "improve" it?

In Business tips, our friend Peter Carruthers recommends ways to save you cash on computer tech.  He also has a huge discount on his valuable small business course bundle:  Useful for POPIA and protecting yourself from business risks.

Our two live online courses, Excel Macros and Excel for Engineers, are still scheduled to alternate monthly.  Or you can do a cheaper self-paced version online.

This month's Excel Tip looks "under the bonnet" at two lesser-known powerful functions for looking up spreadsheet data.

We end with more wisdom (or otherwise) from Computius.

May I Pick YOUR Brain, For Once?

Right, loyal readers, it's payback time.  For a change, I need your help, please.

I need my "Letter of Appointment as Master’s Representative" to be Executor of Judith's estate. To get it, I have to supply the Master of the High Court with a "Completed Inventory (form - J243) showing all the assets of the deceased. NB: Proof of the value of the assets must be provided."

Do you know of someone who can do a legally-recognised valuation of a small amount of clothing, books, glassware and cookware at reasonable cost?  Please email me here.

Programmers and the Irresistible Urge to Tinker

"A program is never finished until the programmer dies", Computius said in June 2017 (he has a good memory: it's on a hard drive).

So it would seem, judging by some software changes we've seen.

Take Zoom, for instance: it's a mature product and doesn't need improving. But a few months ago, for no obvious reason, they decided to automatically hide the toolbar when one is not pointing the mouse at the zoom window. That does give one a little more picture space. But it comes at the cost of not being able to see at all times whether your mic and camera are off or on (dangerous).

More notorious would be Microsoft Excel. Few of the changes from the 2010 version to 2013 and later can be called improvements. The newer versions use more screen space to display the same things. You can now change the Office Background and Office Theme, features of little value.

Meanwhile, the great loss is the MDI. The Multiple Document Interface displayed many spreadsheets in the same Excel window. The new Single Document Interface (SDI) puts every spreadsheet in its own window somewhere on screen. Excel power users hailed this as a catastrophe. They posted thousands of complaints on the Excel UserVoice forum “Restore MDI file handling (open all files in one window); Kill SDI (each spreadsheet opens in a seperate [sic] window)” before Microsoft killed the forum. All of which Microsoft ignored.  Microsoft knows what users need, much better than mere users do.

I suppose we should be grateful for some mercies. In the 2016 version, Microsoft abandoned the ugly uppercase ribbon tabs they foisted on us in Excel 2013.

Nor is this limited to software. Have you seen some of the cars on the road recently? They might fit well in Star Wars, but hardly in suburbia.

Computius has more to say on this below.

Business Tip: How to Save Real Money on Information Technology

Our friend and mentor, Peter Carruthers, writes:

Excel for Engineers Online Live Course

Next Course:

Venue: Live Online via Zoom.  65-page PDF manual included, and 14 examples files.
Special Price if paid not later than two weeks before the course: Only R4,500 plus VAT per trainee.
Thereafter R5,000 plus VAT each.

Course Creator and Presenter: Rick Raubenheimer B Sc (Eng) (Wits) (1975).

For more information and to book a course online or in-house at your company, click here and send the resulting email.
Or email info@softwareafrica.co.za

Excel for Engineers Online Self-Paced Course

The Excel for Engineers live course is also available on-line as a self-paced course.  The same manual and examples.  The same trainer. No live support, however.  But done at your own pace at times that suit you.  Take half an hour a day and complete it in a month.  Spend an hour a day and finish it in two weeks.  Or dedicate two days –a weekend, perhaps?– and crack the whole course.

Sign up now and get these Bonuses:

1. Report, "Are You Making These Microsoft Excel Mistakes?"
2. A Free Support Group
3. Our Excel file "Excel Shortcut keys & My Macro Shortcuts" (you'll want this once you start building macros).

Take our self-paced online course in your own time and venue.  All the value at a quarter of the price of the live course.  Backed by a 30-day money-back guarantee.

Excel Tip #214 -- Lookup Examples V: More on INDEX(MATCH)

In In Excel Tip #210 we started a practical example of how to pick out a name from a list. You can see the full article on our Excel for Engineers blog.

To cement your learning, let's take a deeper look at the INDEX and MATCH functions.

For some lookups, INDEX with MATCH may be the solution.  Together, they are more robust and versatile than ;VLOOKUP, HLOOKUP, and LOOKUP.

MATCH returns the position of the matched item in a one-dimensional list. It can do an exact or approximate match.  MATCH(lookup_value, lookup_array, 0) does an exact match.

INDEX returns the value at given row and column numbers in a range.  INDEX (array, row_num, column_num) where row_num and/or column_num are the result of a MATCH.

In this example, from row 18, column A contains the results of the formula as documented in column B.

In cell A18, =MATCH(“Gizmo”, A4:A13, 0) looks down the orange range A4:A13 for an exact match.  It finds “Gizmo” in the fifth position, and returns the value 5.

In cell A19, =MATCH(2022, B3:E3, 0) looks across the blue range B3:E3 for an exact match.  It finds the year 2022 in the fourth position and returns the value 4.

In cell A20, =INDEX(B4:E13, A18, A19) then uses the previous two results to return the value 1463.  It is at the 5th row and 4th column of the pink range B4:E13.

In cell A21 we show how the whole process can work in one cell, using =INDEX(B4:E13, MATCH(“Gizmo”, A4:A13, 0), MATCH(2022, B3:E3, 0))

Nesting the MATCH functions inside the INDEX makes the formula more compact.  But the intermediate calculations make the process easier to understand. It is also easier to debug!

Note that the lookup ranges MATCH uses do not have to be next to –or indeed anywhere near– the range from which INDEX gets the final value.

Read more here on our Excel for Engineers blog.

In the next Excel Tip, we will look at another aspect of Excel.  Stay tuned!

Microsoft Excel Macros live online course

Macros let you do repetitive work in a flash, instead of repeating the same boring stuff manually.  Save hours not working late, and spend more time with your family ...or the dog.  You can now attend the whole course live online over two days from the comfort of your own computer.  Do you have a good knowledge of Excel, and now want to program your own time-saving applications?  Then this course is for you!

Scheduled Courses:

Venue: Live Online via Zoom.  You get an 85-page PDF manual, and valuable examples.
Early-Bird Price if paid not later than two weeks before the course: Only R4,500 plus VAT per trainee.
Thereafter R5,000 plus VAT each.

For more information including the curriculum, click here.  To book a course online or in-house at your company, click here and send the resulting email.  Or email info@softwareafrica.co.za.

Or do it Online in Your Own Time:

Instead of a doing the whole Excel Macros course over two days, you can do it online in your own time, more cheaply.  The Software Africa Quick 'n Easy Turbo-Start Excel Macros course is now online.  Take it now!

Computius Say:

Upgrade Software: "Take old bugs out, put new ones in".

Remember,  We can make your business run better by:

• Training your company live online, or live in-house, or via a self-paced online course.
• We train on Excel, Word, Access, and Windows (all levels from beginners to Programming).
• Automating your in-house processes with Excel macros, a custom program or database.
• Promoting your corporate image with a set of templates on Microsoft Word or Excel, linked to your address list.
• Speeding up your processes by linking to your database or spreadsheet.
• Solving your QuickBooks, Microsoft Access, Excel, Word, Visual Basic, and PowerPoint problems.
• Supplying software: We sell and support various accounting, timesheet, scheduling, and productivity programs.
• Providing programs for the Engineer and Engineering Geologist: dotPLOT, ZWCAD, progeCAD, Autodesk AutoCAD LT and Revit LT.

All the Best from

Communication in Action cc trading as Software Africa

Reg.  2009/007863/23 ~ VAT No 4500104387 ~ 126 Kelvin Drive, Morningside Manor, Sandton, 2191 South Africa.

Disclosure:  Some links we share might be affiliate links we promote in return for a commission.  We only promote things we've actually tried and think could be great for you too.