Sad News ~ Online Business Tip ~ Excel Tip ~ Training Tip
Welcome to the Software Africa Newsletter: Subscribed to by the Select, read by the Elite, acted on by the Exceptional.
This month we have sad news at Software Africa.
In Business tips, our friend Peter Carruthers has a special offer that could save your business. It expires Friday 15 July, 23:59.
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 next piece of the puzzle of looking up data.
We end with wisdom (or otherwise) from Computius, the computer Confucius.
| We are desperately sad to announce the death of Software Africa’s Sales Director,
Judith
Elizabeth Taylor. Judith was a strong businesswoman, entrepreneur, Distinguished Toastmaster, Secular Society volunteer, and passionate environmental activist. A Life Member of Earthlife Africa, she received the 2020 National Wetland Award for services to wetland conservation. Judith was my business partner, wife of 31 years, and life companion since 1986. She died on Sunday afternoon of a chest infection following a series of illnesses since 2020. Her family was at her bedside in the Sunninghill Hospital ICU. We are holding a secular memorial ceremony and scattering of ashes on Sunday 24 July 2022, 10:00–11:30, online and in person. Please RSVP to me by email reply, or WhatsApp or Telegram at +27 82 389-3482 if you will be attending in Morningside Manor, Sandton, Johannesburg, or want the Zoom link. The business of Software Africa will continue largely as before. As we tie up loose ends there may be delays in getting to your work; we apologise for this and trust that you understand. Thank you for your attention, and RICKgards |
Our friend and mentor, Peter Carruthers, writes:
| This month marks the first anniversary of POPIA, the Protection of Personal Information Act.
More than a dozen massive data breaches happened this past year. More than 85% of all breaches worldwide result from human error. Crooks target our staff because they're the weakest link in every business. Stealing data is hugely profitable. The crooks know that most of your staff don't know what can go wrong. In large corporations, employees face at least one malicious email each day. This email looks like it comes from a client, supplier or friend. It tempts them to click on something innocuous. Crooks want to use your employee as a portal to load their virus, malware, or ransomware. Every major breach in South Africa resulted from a staff member doing something they shouldn't have. I recently hired a freelancer. She has two university degrees. You'd think this is enough. Not so. She is single. She uses dating sites. And a whole bunch of social media. She had no idea about data privacy. That lack of privacy awareness and where dangers might lurk puts my business's future at risk. She relies on me to pay her on time. I rely on her to safeguard the codes that open all the locks to my business. I asked one simple question. Her answer told me she didn't know enough about data privacy to be able to protect herself, let alone me. I put her through our privacy awareness course. One hour later, she knew enough about the dangers for us to start together. Click here to view a short (5-minute) video about that one question that will make your future much less fraught. And how to solve it. (There is also a transcript if you prefer to read it.) This one question shows you if this person is safe for your business. Or whether this person needs some training first. You can even ask your current staff this question. You'll feel a lot safer when you have their answers. It is as simple as that. Please view the video here. There is special pricing ending on Friday (tomorrow) at midnight. Warm regards Peter |
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
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:
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.
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 will learn 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.
Now for the formula to display the name of the winner...
The full formula in B19: =IFERROR(INDEX($H$4:$Q$5,1,MATCH(MAX($H$11:$Q$11),$H$11:$Q$11,0)),"")
That formula for the winner contains several nested formulas: =IFERROR(INDEX($H$4:$Q$5,1,MATCH(MAX($H$11:$Q$11),$H$11:$Q$11,0)),"")
Let's break it down: MAX($H$11:$Q$11)
finds the maximum score of the sums in row 11.
Next, we have MATCH(MAX($H$11:$Q$11),$H$11:$Q$11,0)
MATCH returns the position number of an item in an array that matches the lookup
value. The parameters are MATCH(Iookup_value, array, match_type). Match_type = 0
specifies an exact match. In other words, it takes the largest value and finds
that it is (in this case) in the 5th column of the array (Astrid) and returns a
5.
Now for INDEX($H$4:$Q$5,1,MATCH(MAX($H$11:$Q$11),$H$11:$Q$11,0)):
The INDEX function returns a value (or the reference to a value) from within a
table or range.
There are two forms of the INDEX function. We are using the simpler form, to
return the value in a specified cell in a range of cells. The syntax is:
INDEX(range, row_num, [column_num])
In this case, we look up in the blue range H4:Q5 (the contestant names in two merged rows), row 1. The column is set with the MATCH formula (5, in this particular case).
This could be improved to INDEX(H4:Q5, 1, MATCH( MAX(H11:Q11), H11:Q11, 0)):
[As an aside, the more complicated form of INDEX allows you to replace the "range" parameter with a series of ranges, separated by commas, and enclosed in parenthesis (round brackets). Then you need one extra parameter to say which of them to use. We won't go there in this tip!]
Finally, to keep it all tidy, we have the IFERROR function. If the first parameter returns an error value, IFERROR returns its second parameter, in this case "" (a blank string). Otherwise, it returns the (non-error) first parameter.
In the next Excel Tip, we will look at the formula to display the runners-up.
Are you too impatient to wait? You can find the full article
here
on our new Excel for Engineers website.
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... 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:
| Thursday 28 and Friday 29 July 2022 (two days, 08:00-17:00 CAT). |
| Thursday 22 and Friday 23 September 2022 (two days, 08:00-17:00 CAT). |
| Thursday 24 and Friday 25 November 2022 (two days, 08:00-17:00 CAT). |
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.
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!
Disclaimer: All errors in spelling, tact, or fact, are transmission errors.
All the Best from
Rick
, now sadly without Judith
Communication in Action cc trading as Software Africa
"Empowering African Business with standard and custom PC programs, databases, and templates using Microsoft technologies"
Reg. 2009/007863/23 ~ VAT No 4500104387 ~ 126 Kelvin Drive, Morningside Manor, Sandton, 2191 South Africa.
Tel: 011 802-6440 ~ Cell: 082 389-3482 ~ e-mail: info@softwareafrica.co.za
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 would be great for you too.
|
This entire newsletter is Copyright © 2022 Communication in Action cc t/a Software Africa. All rights reserved. Information may be reproduced in full context as long as credit is given. Newsletter Index | < January 2015 | < January 2016 | < January 2017 | < January 2018 | < January 2019 | < January 2020 | < January 2021 | < January 2022 | < February | < March | < April | < May | < June | Back to top | Updated 14 July 2022 | e-mail Webmaster. |