Software Africa Newsletter - August 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 we have a rant about how dysfunctional our Government is.  Nothing new, right?

In Business tips, our friend Peter Carruthers has a webinar TONIGHT that could bring you several million more prospects. Tempting?

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

This month's Excel Tip looks at the final piece of the puzzle of looking up complex data.

We end with wisdom (or otherwise) that Computius has borrowed from a Russian tactical genius.

Is the South African Government Dysfunctional?

It has been an "interesting" month, in the sense of the old Chinese curse.

With the sad death of Judith, I have had to contact various government departments.  Or try to...

The Master of the High Court, Johannesburg, is listed on the Justice Department website with E-mail: MastersJohannesburg@justice.gov.za / LPule@justice.gov.za and Tel: 011 429 8000/1/2/3.  The MastersJohannesburg email address gives "550 Invalid Recipient".  One later discovers, on downloading their Contact list for officials PDF [Updated 20 Apr 2022] that they know it isn't working.  But nobody has bothered to fix either the website or the email.

I emailed Mr Pule and have had no reply, not even a read receipt. The phone numbers 011 429 8000/1/2/3 all give either “exchange equipment engaged” or “the call cannot be completed due to a temporary error” over several days.  I am now working my way through the contact list, and finding it out of date.

I have a similar problem with the Department of Home Affairs (DHA) Section Marriages, Pretoria (I run the Marriage Officer project at the SA Secular Society, and arrange for our candidates to take the DHA Civil Union Act exam).  Their phones seem to be constantly "discontinued or no longer in service".  Perhaps they don't pay their phone bills?  At least my contact answers emails -sometimes.

Then there is the Companies and Intellectual Property Commission (CIPC).  I have to remove Judith as a Member of the CCs.  I reported this on the CIPC website. They emailed me a CK2 form for each company, to sign then scan and send back to eServicesCK2@cipc.co.za, together with all certified ID copies. I did that.

However, their system seems to be programmed to reject all emails with the subject "REJECTION DUE TO NON-COMPLIANCE" and a list of 13 possible causes, most of them unclear. The only one that looked like it might apply, seemed to be "Only reflect the tracking number OF THE NEW COMPANY TRACKING NUMBER, COR39 or CK2 in the subject line when submitting documents - the name reservation tracking number should not be used". What this means is not obvious. I tried various subjects, none of which worked.

I then tried to lodge a ticket to solve the problem.  All went well until I hit the "Submit" button.  Then I got
System Error
You are getting this message bacause [sic] the system has encountered an error. You need to refresh in [sic] to continue with your operation.
I tried 3 times, with the same result.  Refreshing the page has no effect.  Looks like a good way to reduce the number of support staff needed.

Google kindly gave me CIPC Johannesburg's phone number, 012 394 9573 (open weekdays 08:00-16:00).  They answered promptly. The lady there was very helpful and explained. The tracking number is what we must use as a subject!  They give it online but don't say what it is for.  They also put it in huge type near the top of the CK2 they send.  But they don’t say anywhere that it is a tracking number!  How are we supposed to guess?

The emails I then sent with the tracking number as subject worked.  Here's hoping they don't require a signature from my dead partner approving the change...

They didn't, but do say (in a TIFF file, mind you):
We have received a CK2 (Amended founding statement) from you dated 12/08/2022
We regret to inform you that the Close Corporation could not be amended for the following reason(s):
Attach certifed [sic] copy of Executors letter obtained from the Masters Office for the deceased, Executor must sign on behalf of the deceased. Include certified ID copy of executor.
The certifying stamp on the id copy of Richard may not be older than 3 months.

They don't tell one that in advance, of course. So it's back to the dysfunctional Master of the High Court!

Business Tip: Do You Want 470 Million More Prospects?

Our friend and mentor, Peter Carruthers, writes:

Excel for Engineers Online Live Course

Following Courses:

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 Facilitator: 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 #213 -- Clever Lookup Examples IV: Formulae to Show the Runners-up

In In Excel Tip #210 we started this example of how to pick out a name from a list based on some data, using a contest scoring form.  You are learning about the Lookup functions MATCH, INDEX, MAX, LARGE, IFERROR, and IF.  See that issue to orientate yourself and see the picture of the judging form.  Then follow the thread in Excel Tip #211 and last time to display the name of the winner.

Now for the final formulae to display the names of the runners-up...

These formulas seem, at first, more daunting:
B20 =IF(IFERROR(INDEX(H4:Q5,1,MATCH(LARGE(H11:Q11,2),H11:Q11,0)),””)=B19,””, IFERROR(INDEX(H4:Q5,1,MATCH(LARGE(H11:Q11,2),H11:Q11,0)),””)) and
B21 =IF(IFERROR(INDEX(H4:Q5,1,MATCH(LARGE(H11:Q11,3),H11:Q11,0)),””)=B20,””, IFERROR(INDEX(H4:Q5,1,MATCH(LARGE(H11:Q11,3),H11:Q11,0)),””))

Remember that H4:Q5 is the Contestants, while H11:Q11 contains their Scores. Let’s use Range Names to simplify matters. Then we get:
B20 =IF(IFERROR(INDEX(Contestants, 1, MATCH( LARGE(Scores,2),Scores,0)),””)=B19,””, IFERROR(INDEX(Contestants, 1, MATCH( LARGE(Scores,2),Scores,0)),””)) and
B21 =IF(IFERROR(INDEX(Contestants, 1, MATCH( LARGE(Scores,3),Scores,0)),””)=B20,””, IFERROR(INDEX(Contestants, 1, MATCH( LARGE(Scores,3),Scores,0)),””))

That is immediately more readable, even without the blue and green that tie in with last time's picture..

We notice that there are only two differences between the two lines:
1) =B20 in B21 vs. =B19 in B20 (in each case referring to the cell above), and
2) LARGE(Scores,3) in B21 vs. LARGE(Scores,2) in B20.

Notice that LARGE(Scores,2),Scores,0) replaces MAX in the Winner’s formula (B19). Where MAX gave us the highest value, LARGE(range, rank) gives us the value at the “rank” position in the ranked list. That is, LARGE(range, 1) gives us the biggest value (= MAX). Then LARGE(range, 2) gives us the second-largest value, and LARGE(range, 3) the third-largest value.

As with the winner, once LARGE has given us the score of the runner-up, MATCH gives its position in the range. Then INDEX looks up the name of that runner-up. Wrapping it in IFERROR makes sure we get a blank instead of an error.

For ease of understanding, we can simplify the formula in B20 to this:
B20 =IF(Second_Place=B19,””, Second_Place)
or
B20 =IF(Second_Place=Winner,””, Second_Place)
Similarly , for the third place:
B21 =IF(Third_Place=Second_Place,””, Third_Place)

Remember that the function is IF(logical_test, value_if_true, value_if_false)

So, in B21, if the third-placed name is the same as the second-placed, return a blank instead. That would be the case in a contest with only two contestants.

Similarly, in B20, if the second-placed is the same as the winner, return a blank instead. It would be unusual to have a contest with only one contestant!

In practice (but not in this spreadsheet), if there are three contestants, only the first and second places are awarded. If there are two contestants, only the winner is awarded.

Commentary: The author of the spreadsheet elected to do a tour de force. They (with much duplication) put the entire formula for each result in one cell each. But this is a good case for doing an intermediate calculation. Determine the Second_Place and Third_Place in another (hidden) cell. Then use a simple formula like B21: =IF(Third_Place=Second_Place,””, Third_Place) to compute the final answer. That is easier to understand and less prone to error.

Would you like to see the full article we serialised here?  Go 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.  85-page PDF manual is supplied, 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:

Putin say: "Two wrongs are only the beginning".

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 that we promote in return for a commission.  We only promote things we've actually tried and think could be great for you too.