Excel formulas from concatenated data

If, like me, you have been trying for ages to create formulas from concatenated data in Excel, then this is the post for you! For those of you who are already glazing over, perhaps skip this post :-)

I use spreadsheets a lot for descriptive analysis of survey data, managing student information, and for marking. It is with marking in mind that I wanted to use a concatenate function for creating a summary page in an Excel workbook. 

I have a tab (sheet) for each student, named with the student name so it auto-fills the student name field (see here for how to do that). That name is repeated on a summary page which is a VLookup so I don't have to fill in the student ID number (and here for how to do that). On that page I wanted to also show a copy of the mark for each student so I could see them all in one place, at a glance.

Concatenate seemed the logical function to compile the formula, but I could never get it to activate. I tried Shift and Enter, but that didn't work. I postponed following up on it. I would periodically search, but my search terms were obviously not accurate enough.

But then, late last year, I found a solution, thanks to tbhesswebber (2013), who posted that "a dynamic formula" was needed for "a template that we copy each month, so when I change the name of the month and I change the year, it will refer to a different folder and/or a different month within the folder". I wanted the formula to refer to a different name for each new marking sheet that I created. Same process, different object.

The solution from tbhesswebber got quite complicated, but my takeaway was that I needed to use "=INDIRECT()" with my concatenate formula nested inside (2013). The concatenate function was: (a) the open quote mark to signify that this was a named tab in the workbook, "'"; (b) the student name of the tab in "Student's Full Name", B2; (d) the close quote mark to close the named tab, "'"; (d) and the sum cell in each tab, or F$1. Then all I needed to do was to enclose this in the INDIRECT element. My formula was:

=INDIRECT(CONCATENATE("'",B2,"'",F$1))

Thank you very much, tbhesswebber!

Sam

Reference:

tbhesswebber. (3 February 2013). Answer to Can I create a formula using concatenate?. https://www.excelforum.com/excel-formulas-and-functions/592520-can-i-create-a-formula-using-concatenate.html

read more "Excel formulas from concatenated data"

Types of Likert scales

For some reason I don't think I have only ever done one post on Likert scales (here). So let's do something about that. 

While I assume most of us will know what Likert scales are, we are always best to start with a definition. So they are a type of assessment question "format developed by Rensis Likert" which provide a much more systematic approach for question analysis than a 'phrase completion scale' question, which is where someone provides their own word (Kempf-Leonard, 2005b, p. 53). Data analysis becomes much more difficult when participants have chosen their own responses; while Likert-style standardisation does remove richness, it allows for more comparison. Likert came up with the idea in 1932 when completing his PhD (Sack, 2020). 

Likert scales are "used to measure attitudes [and] have an introductory stem consisting of a positively or negatively stated proposition followed by a graduated response key composed of adverbs (e.g., 'strongly') and verbs (e.g., 'agree')" (Kempf-Leonard, 2005b, p. 53). A number can be allocated to each option which allows us to more easily measure the strength of the responses, to weight the answers provided. The most common is for a five point scale may have five points included, or a six point scale may go from five to zero (see the image accompanying this post). Negatively asked questions are often scored in the opposite direction.

If we are putting together a questionnaire, we should try and use the same - or very similar - Likert scales throughout our survey if we can. This is to prevent tiring our surveyees. We only want them to have to think about answering the question, not HOW to answer the question as well.

There are a range of Likert scale anchors ("the descriptors of the various response categories", Kempf-Leonard, 2005a, p. 504) that we can use. A few examples follow:

• Frequency scales are seeking to find how often - or when - participants do something. Questions might be "How often do you read this blog?" with scales such as "always, very frequently, occasionally, rarely, very rarely, never" (Sack, 2020) or "daily, every second day, twice a week, weekly". There are a lot of standard frequency scales (Vagias, 2006).
• Agreement scales ask how much participants agree or disagree with the question or statement (Sack, 2020). "This blog has new content every day", and "Strongly disagree, Disagree, Somewhat disagree, Neither agree or disagree, Somewhat agree, Agree, Strongly agree". 
• Importance scales are asking participants for a personal judgement. In some ways this is similar to the next option (quality scales) so phrasing should be careful and well-crafted so as not to be leading (Sack, 2020). Questions such as "Reading the blog every day is..", "Not at all important, Low importance, Slightly important, Neutral, Moderately important, Very important, Extremely important" (Vagias, 2006). 
• Quality scales are seeking information about participant standards (Sack, 2020). Questions might be "Blog posts contain good information" These too are very common, and the example image this post is an example: "Excellent, Very good, Good, Fair, Poor, Very poor"
• Likelihood scales ask participants about what is true for them. A seven point scale to answer the question "I read this blog every day",  would be "Very untrue of me, Untrue of me, Somewhat untrue of me, Neutral, Somewhat true of me, True of me, Very true of me" (Vagias, 2006). 

I hope this is helpful!

Sam

References:

Kempf-Leonard, K. (Ed.). (2005a). Encyclopedia of Social Measurement (Volume 2 F-O, 1st ed.). Elsevier. 

Kempf-Leonard, K. (Ed.). (2005b). Encyclopedia of Social Measurement (Volume 3 P-Z, 1st ed.). Elsevier.

Sack, H. (5 August 2020). Rensis Likert and the Likert Scale Method. http://scihi.org/rensis-likert/

Vagias, W. M. (2006). Likert-type scale response anchors. Clemson University. https://www.peru.edu/oira/wp-content/uploads/sites/65/2016/09/Likert-Scale-Examples.pdf

read more "Types of Likert scales"

TextJoin function in Excel

Where we have simple requirements, Excel can be very useful for analysing, grouping and clustering survey data. However, to get the most out of the simple analysis which Excel does, being aware of some of the on board tools can certainly make a straight-forward job even more powerful.

For example, there are several ways to concatenate elements within a range. We can use a formula such as concatenate, or we can use the more streamlined version, TextJoin.

TextJoin allows us to collapse materials and show a spread of answers in one cell. It allows the formula to ignore any blanks in the formula and only concatenate those items which contain more than zero. This can be particularly useful if we are compiling a range of answers into one field. The image accompanying this post shows how all the fields are pulled into one, including specifying a delimiter: in this case, a space, and a comma.

A deliminator could be a hyphen, a line of text, or simply a space.

The structure of putting together a TextJoin function is as follows, with the formula first, and the example following:

=TEXTJOIN(delimiter, TRUE, Cell_range)

=TEXTJOIN(", ",TRUE,B3:B8)

This is a very useful formula.

Sam

References:

CFI. (2019). TEXTJOIN Function. https://corporatefinanceinstitute.com/resources/excel/functions/textjoin-function/

ExtendOffice. (2014). Concatenate Cells But Ignore Blanks. https://corporatefinanceinstitute.com/resources/excel/functions/textjoin-function/

read more "TextJoin function in Excel"

What to do with incomplete surveys

I had an undergraduate student ask recently what to do with incomplete surveys: should they include all their data, or not?

This is an interesting question. There is no right or wrong answer: only the answer we choose for each project, and how systematic and careful (to quote Bennet, 1991) the justification is in thinking our way through to our particular decision.

The choices - as I see them - are for us to use:

• Only completed surveys; or
• Anything less than complete - usually surveys competed to a certain point onwards. This will be to wherever most makes sense for us to say "This info is essential and without this, the collected data adds nothing to my study"; or
• Any survey which has been started.

We simply (a) use evidence and expert method to come to our decision, (b) write up the justification in our method, underpinned with cited evidence, and (s) apply whatever we have decided consistently across our entire data set.

When we discuss our data profile - which may fit in our methods chapter or at the beginning of our results chapter - we talk about how many surveys were incomplete, how many were complete, and whether there was, or was not, a pattern to be found in where people exited the survey. Thus the shape of the data collection also forms part of our data.

Further, if we allow partially complete survey data, when we speak about this in the body of our text, we also make it explicit - transparent - just how many people have responded to each question. For each graphic (graph) we use, we also include the number of respondents on the graphic (i.e. n=91), so readers can understand exactly how many people responded to each question.

If there were a 'significant' number of incomplete surveys, we might also write this up in the limitations in the methodology. We also need to justify where 'significance' arises; for example, we might use materiality, where we assume anything greater than 5% is significant.

If there were a large number incomplete (such as 25%), which we would probably feel then undermined the quality of the research to a point where the reliability of the research question's answer was jeopardised, we would mention that in our substantive limitations within the conclusions chapter.

It is quite interesting to think about these things ahead of time, but there is a lot to data collection which we don't realise we haven't thought about until we strike it!

Sam

• Reference: Bennett, R. (1991). Chapter 5: What is Management Research? in N. Smith & P. Dainty (Eds) The Management Research Handbook (pp. 67-78). UK: Routledge.

read more "What to do with incomplete surveys"

Likert scale wording

Those of us who use surveys regularly are very well aware of the legacy that Professor Rensus Likert has left us, in the Likert scale.

During and after his PhD, Professor Likert critiqued existing survey and interview techniques, and outlined more robust processes and tools. The clarity of his thought has meant that we still use his improvements today (Britannica.com, n.d.b; Edmonson, 2005; Likert, 1932; Likert, Roslow, & Murphy, 1934), and those scales are now named after him.

A Likert scale (always with a capital "L", as this is a proper name) has been defined as a "rating system, used in questionnaires, that is designed to measure people’s attitudes, opinions, or perceptions" (Britannica.com, n.d.a), and as "certain discernible groups of social responses [where... the]response to an attitude, although not inflexible or rigid, [...]move[s] only within a certain range" (Edmonson, 2005, p. 127).

And it is these groups of social responses which gives us some - now - pretty standard sets of scales. They usually come in spans of three, five or seven, with generally accepted wording, designed to work with questions which are written so that the scales provide a logical answer. For example:

• 5: Excellent - Above average - Average - Below average - Poor
• 7: Exceptional - Excellent - Very Good - Good - Fair - Poor - Very Poor
• 5: Satisfied - Somewhat satisfied - Neutral - Somewhat dissatisfied - Dissatisfied
• 7: Very satisfied - Moderately satisfied - Slightly satisfied - Neutral - Slightly dissatisfied - Moderately dissatisfied - Very dissatisfied
• 5: Always - Often - Sometimes - Seldom - Never
• 7: Always - Fairly Often - Somewhat Often - Sometimes - Seldom - Rarely - Never

There are a couple of online sources to consult to get some standard wording for Likert scales here (Sincero, 2012) and here (Vagias, 2006).

It is fascinating that it took the human race until 1932 to think of something so simple and elegant.


Sam

References:

• Britannica.com (n.d.a). Likert Scales. Retrieved from https://www.britannica.com/topic/Likert-Scale
• Britannica.com (n.d.b). Rensis Likert. Retrieved from https://www.britannica.com/biography/Rensis-Likert
• Edmondson, D. R. (2005). Likert Scales: A History (pp. 127-133). CHARM: the 12th Conference on Historical Analysis & Research in Marketing, 28 April - 1 May 2005, Queen Mary, Long Beach CA, USA.
• Likert, R. (1932). A technique for the measurement of attitudes. Archives of Psychology, 22(140), 1-55.
• Likert, R., Roslow, S., & Murphy, G. (1934). A simple and reliable method of scoring the Thurstone attitude scales. The Journal of Social Psychology, 5(2), 228-238. https://doi.org/10.1080/00224545.1934.9919450
• Sincero, S. M. (2012). Survey Response Scales. Retrieved from https://explorable.com/survey-response-scales
• Vagias, W. M. (2006). Likert-type scale response anchors. Retrieved from https://www.peru.edu/oira/wp-content/uploads/sites/65/2016/09/Likert-Scale-Examples.pdf

read more "Likert scale wording"

Great Online Survey Tips

SAGE Publications Ltd puts out a research newsletter from time to time, and in a recent edition, interviewed Mario Callegaro who works for Google in London as a researcher, co-author of Web Survey Methodology (buy it here), and founder of http://www.websm.org/, a website designed to help researchers get their online surveys just right.

During the video, Mario talks about what online surveys are, the pros and cons of using them, and the use of online panels.



A very useful overview!

Sam

• Reference: SAGE Publications Ltd (2017). Mario Callegaro Discusses Web Survey Methodology. Retrieved 21 November 2017 from http://methods.sagepub.com/video/srmpromo/yoZr5z/mario-callegaro-discusses-web-survey-methodology?utm_source=Adestra&utm_medium=email&utm_content=play&utm_campaign=methods17nov&utm_term=

read more "Great Online Survey Tips"

How do I create a survey?

Want to survey your team? Your customers? Your potential market?

The easiest way I know to do this is with SurveyMonkey (here). You can easily sign up for a free account, and have a play. The free accounts have some limitations: your surveys are limited to ten questions, and you cannot export linked data. By that, I mean that each question is exported individually, not still connected to the rest of the survey data that your respondent has entered. For example, if you asked if participants were male or female, and later asked them if they preferred red or blue, you would not be able to tell how many men chose blue. You can't cross-tab your results with a free account.

That is not a mission killer while you are teaching yourself how to use the software though, and SurveyMonkey is pretty easy to learn. To create a survey, you simply drag-and-drop questions onto your 'design survey' tab, and then fill in the question and answer text. Each question type has 'help' text floating above it, so you don't need any additional Help windows.

SurveyMonkey have their own channel on YouTube, which you can access here. You will find lots of 'how to' videos which make learning so much easier than a static help file.

After you have explored the free accounts and know what you are likely to use this for, I think it is worth signing up for a professional account. This allows you to export your linked data, to ask unlimited questions, and more question and survey logic options. I have had a paid professional account for a number of years, and the NZD$300 investment has proven worthwhile.

With a paid account you can also use skip logic on questions. Each answer to a question can send the respondent to another question (or page). For example, someone who selected "Blue" to their favourite colour could be routed to a follow-up question, such as "Why is that?". Even better, you can insert piped text, where the you can incorporate the earlier answer in your follow up question. So where Q.3 was a multiple choice question about favourite colours (eg, Red, Blue, Yellow, Green), Q.4 can ask, "Why did you prefer Blue?" and Q.10's multiple choice options can include [favourite colour] as a response, so you can learn more about that blue answer (Schindler, 9 January 2016).

The analysis tools will graph and cluster your data for you, though, as mentioned before, the free accounts have limited ability to download data. With a professional account, SurveyMonkey gives you quite a lot of options in analysing your data once you have collected it. You get a range of formatted export choices, as pdf, PowerPoint, Excel or CSV. You can also export your linked raw response data to Excel, Google Sheets, pdf or SPSS, then further crunch your numbers off-line.

I wouldn't consider getting a paid account until you have signed up for a free account and have gained some mastery. Experiment with polls and other small surveys until you get confident with asking questions and know how much data complexity you need. There is no point collecting data if you aren't going to do anything with it.

Save getting a paid account for when you are ready to need quite layered information.


Sam

• Reference: Schindler, E. (9 January 2016). SurveyMonkey Review. Retrieved 1 July 2017 from http://au.pcmag.com/surveymonkey/40104/review/surveymonkey

read more "How do I create a survey?"

SurveyMonkey tip: Using Nicknames

When we create surveys using SurveyMonkey, we will often need to have two titles.

One title is for our survey participant, which will appear on each page of our survey. Think of this as our marketing title. It needs to make sense from the end user's point of view. For example, a piece of research I have done with a colleague is entitled:

Survey Title: NMIT Student Recruitment & Screening Survey

The second title we are also going to need is a file name to will help us manage and identify each survey. If our SurveyMonkey account is a company account, we are best to have a naming protocol that helps us identify who owns each survey, when it was created, and when it should be destroyed. I set up a naming convention for NMIT's research questionnaire file titles, as follows:

[Course code] [Year][semester letter]* [Your Surname] [Short title for your survey] ([Destroy by Year][semester letter])

We simply replace the square brackets and content with our information. For example:

File Name: MGT737 2017a Young ATS Research Project (do not delete)

However, SurveyMonkey doesn't make it obvious that we can create two titles.

But to do it, all we need to do is to go into our survey design, click the title field, click edit, click the "Add a nickname" tickbox; then enter our file name, using a sensible naming protocol. To view a quick video on how to do this, go here:


Using two titles makes managing surveys much easier.


Sam

* The semester letter is 'a' for semester one, 'b' for semester two, 'c' for summer semester.

read more "SurveyMonkey tip: Using Nicknames"

Survey Construction Tips

Each semester when some of my research students decide to use surveys as their primary data collection instrument, I go through some of the same theory again.

But each time I do it, I get more clarity in explaining exactly why we do what we do.

All surveys need an informed consent section at the beginning, with a "next" button that by clicking, our participants agree to our research conditions.

We should always get volunteers to test our questionnaire, and ask them to time how long it took them, so we can put a realistic time estimate in the informed consent section; so our research participants know what they're letting themselves in for.

I find it very interesting how theory easy it is for us to present our survey questions in a way that will "turn off" our research participants. By that, I mean that we tell our research participants in our consent section that our survey data will be completely anonymous; we get them to agree to participate by clicking the next button; then the first questions we ask are very personal questions about demographics. Our participants immediately feel that we are invading their privacy.

While what we are asking them does not necessarily make them immediately identifiable, it undermines the spirit of our psychological contract with our participants.

Instead, we need to warm up our participants, by asking them questions that are more related to our actual research question. We start with questions which are fairly simple, then as our participants get used to how we ask questions, we can ask our more important and complex questions.

Then lastly, we can have a section on what are the absolutely important demographics that we absolutely have to know in order to cross-tab without other answers for our research to be successful.

We aim to have only 10 to 15 questions overall. Part of our question will include the instructions on how each question needs to be answered. We ask all our questions - where possible - and exactly the same way. If we are using Likert scales, we use the same Likert scale throughout our questionnaire.

We cluster similar questions alongside each other. We use closed questions before asking open ended questions, to give our participants time to consider their answers before they give them.

I would recommend we normally close with a large and welcoming text box, asking participants if they want to provide any additional information, or make any comments on the survey. This text field often contains an amazing amount of "gold" when you extract and analyse your data.

After this we ask any demographic data questions that we need. By this stage we have built a relationship, of sorts, with our participants; they will be more willing to trust us.

Then lastly we have a thank you page, which also contains our email address, should the participants wish to get in touch with us.

This should be common sense. But it is surprising how often we can all forget these simple principles.


Sam

• References: Remenyi, D. (2012). Field Methods for Academic Research: Interviews, Focus Groups and Questionnaires. UK: Academic Conferences Limited.

read more "Survey Construction Tips"

How good is it having your dog at work?

The following post is from a student of mine, who is researching how many businesses have a "bring your dog to work" policy.

Picture this, it’s Monday morning, you’re sitting at your desk, feeling a large dose of ‘Monday-itis’ hanging over your head like a black cloud, the work week looming out in front of you like a foggy tunnel.

The 55 emails that arrived over the weekend and are sitting in your inbox boldly daring you to open them before you look at what lies ahead on the weekly agenda of duties. The phone rang several times before you even got to the staff room to make a cup of coffee and there’s no way you can nip out to the coffee cart until your tea-break without being noticed. You get the picture, right?

Then you look down, just under your desk lies your best mate, munching happily on a rawhide chew with blissful contentment, the critter who adores you like you’re the best person that ever lived and isn’t afraid to show it every time you walk into the room. Immediately your blood pressure drops back to normal, you take deeper breaths, and your Oxytocin levels soar (that’s the hormone that induces feelings of calm and relaxation).

If only this was a true depiction of most NZ organisations – not the ‘Monday morning part’ but the ‘furry friend under the desk’ part!

There’s plenty of research out there showing how beneficial it is to have a pet friendly workplace. My question is – how many managers actually know what the benefits are? What is stopping local organisations from allowing employees to bring their furry whanau to work, to the benefit of all?

Well, I’m researching this at the moment, in my local community of Nelson, as part of my under-graduate management research project, so perhaps I can provide some answers in another blog post a little further down the track.

What I can tell you is that research into this topic is not a new thing. It was noted back in the 1990s how therapy dogs in hospitals had a positive effect on staff morale and Randolph Barker (yes, ironic huh!) first touted the idea to researchers, not just in the medical field, that this topic had genuine merit and deserved further investigation.

Further investigation it certainly has had and my contribution to this is seeking to find out how well known these findings are?

If you hold a management position in any business, any industry, any size, any type, in the Nelson Tasman region, you can help unravel this mystery by participating in my survey.

It only takes 5 minutes (I promise) and you and your answers will be totally anonymous – so please - help me out, so I can get an answer to these questions.

Here’s the link: https://www.surveymonkey.com/r/GHGGQZ9

Natalie

References:

• Allen, K., Blascovich, K., Mendes, W.B. (2002). Cardiovascular reactivity and the presence of pets, friends, and spouses: the truth about cats and dogs. Psychsomatic Medicine, 5(64), 727-39.
• Barker, R. T. (2005). On the Edge or not? Opportunities for Interdisciplinary Scholars in Business Communication to Focus on the Individual and Organisational Benefits of Companion Animals in the Workplace. Journal of Business Communication, 42, 299-315.

read more "How good is it having your dog at work?"

Newsletter Issue 114, May 2006

Sam Young Newsletter

Issue 114, May 2006

Hi guys,

What has Microsoft in store for us now? Read about Office Genuine Advantage below.

Read the results of the UK Work Satisfaction Survey, and see if your people are satisfied.

Career Practitioners' Tools   TLAs for SMEs        Tips, Short & Hot Keys        Hot Linx

Don't forget, if you want to be taken off my mailing list, click here to send me a reply e-mail and I will remove your name.

Office Genuine Advantage

Office Genuine Advantage (OGA) is a new Microsoft method for checking whether the copy of Office on your PC is a 'legal' and properly licensed version of the software. It's an extension of the Windows Genuine Advantage (WGA) scheme which has been operating for some time.

When downloading add-ins, templates and patches - and with your advance approval - OGA checks if your Microsoft Office XP, 2003, 2007 or individual Office 2002+ standalone Word or Excel application is properly licensed. It only kicks in if you want to download extras from the Microsoft web site.

You are directed to a download page with a button which runs the OGA system; you install an ActiveX control (which Windows will handle for you after the normal security warning). The ActiveX sends info on your software to Microsoft, and, if you have a legal copy, the web page will refresh and let you download the particular extra that you are seeking.

Now that the ActiveX control is installed, it will run on all future downloads which require validation. And, in the words of Woody's Office Watch at http://office-watch.com/office/archtemplate.asp?current "Because there are not enough acronyms in the world the [ActiveX] control is called OVA - 'Office Validation Assistant'. If you can't or don't want to install the ActiveX control there are alternative but slower methods of validation."

Currently OGA is only compulsory for foreign language Office versions, but that will change.

Woody's Office Watch goes on to say with regard to network versions of Office: "Network administrators will be unhappy that there's no tool to validate Windows or Office across a corporate network. This means employees will have to validate individually, the time taken and support costs might be small for one person but can add up across a large business. Astonishingly, Microsoft is asking for feedback about producing such a tool, as if there's some question about enterprise customers even wanting such a thing. Such a tool should be part of the first release of OGA not an afterthought."

According to Microsoft the 'only' information collected by the OGA is:

• Windows product key
• Office product key
• PC manufacturer
• Version of operating system
• PID / SID - software and hardware IDs
• BIOS information and MD5 checksum
• Hard drive serial number
• User and system locale (language setting and version in Windows)
• Your IP address (the Microsoft web site admits to collecting this information elsewhere on their website, not in their official OGA information)

Interestingly, Microsoft says there's no information sent to identify or contact an individual. Hmm. But they have my IP, all my registration data and can identify my individual PC! How can they NOT positively identify me?

The fact that Microsoft is collecting more information than they need should worry us. There is no independent confirmation of the OGA process, so it really comes down to our level of trust in Microsoft.

The issues as I see them are:

• Are Microsoft going to do anything with the unique user information they are collecting, and
• Can we trust them to get our registration information and their collection processes accurate.

If OGA does not validate legitimate Office users, here's hoping Microsoft will learn from the earlier product activation testing and be more open to the possibility that their system is falsely reporting. If not, have you ever tried arguing with a large corporation?!

And what Microsoft suddenly start selling our information for targeted marketing? They would really be mad not to...

 

UK Work Satisfaction Survey

The "Worklife/Herman Trend Alert Newsletter" in May reported on a work satisfaction study, conducted by Sirota Intelligence in the United Kingdom.

Study researchers surveyed a massive 203,000 workers in the UK, on how they felt about their work.

Sirota found that the most satisfied (perhaps unsurprisingly) said they had just the right amount of work. The least satisfied were those with too little work, followed by those with too much work.

Sirota's Chief Executive, Jeffrey Salzman, said that the findings showed that most workers did not want just to 'get by' in their jobs. "Most people come to work enthusiastic and want to make a real contribution. Those who feel they have too little to do – and possibly feel unappreciated by their employers – have lower levels of overall satisfaction and a waning sense of enthusiasm."

When people feel under worked, they also tend to feel undervalued and their job insecurity increases, making them just as vulnerable for employee turnover as the hard-working employees who feel over-burdened, perhaps carrying the loads of others. There is obviously a cost to overworking employees.

Now this study confirms that there can be a serious cost to under working people as well. Today’s workers want to be productive — for job security and for the sense of making a difference for their employers and their employers’ customers. People do not want to be under-worked, so employers need to balance their workers expectations more carefully with company expectations.

What came out from this study is a movement away from mediocrity. People that have the right amount of work to do were happiest, even though that workload may be less that they actually could carry. The least satisfied actually wanted more work; they did not want to feel mediocre, ineffective, or less than valuable to their employer and the world.

Tomorrow’s employees will want to do more, and feel appreciated for their contributions.

Herman Trend Alerts are written by Roger Herman and Joyce Gioia, strategic business futurists, Certified Management Consultants, authors and professional speakers out of Northbridge in New South Wales. Check them out at www.worklifeint.com

Career Practitioners' Tools

There are a number of good career tools available at relatively low cost, useful for working with a range of ability levels and career aspirations.

Firstly, the good, old-fashioned card sorts. There are a number available, with three favourites being:

• 'Kiwi Cards' card sort produced by Career Services. Go to www.careerservices.govt.nz, then click on Resources, Products and Services, on the right hand side of the page there is a heading which says 'Games'. Under this is 'KiwiCards'; click on the link. There is a page of information about them and at the bottom information on how to order or download them
• Preferred Skills, Career Planning Values and Work Settings card sorts designed by Paul Stevens and produced by Worklife Pty Ltd in Australia at www.worklife.com.au
• Industry Training Organisation cards also available as a ‘virtual’ set on www.itf.org.nz

Some hardcopy resources that have stood the test of time are:

• 'The Perfect Resume' by Tom & Ellen Jackson. This book is an absolute purler for those who are wanting to take a self-directed voyage of discovery into their work history and predict their future. View it at http://www.careervictory.com/your_perfect_resume/new_perfect_resume.html
• 'What Color is my Parachute' by Richard Bolles. This book remains the career practitioner's master guide for working with nearly everyone. You can view a newsgroup online at http://www.parachute.com/individuals/content/newscenter/2005/06/09/bolles/
• Career Education Resources, especially for those with special needs, from User Friendly Resources at http://www.userfr.com/latest.asp?Country=NZ&Sector=Sec

Some online & software favourites include:

• KiwiCareers Job summaries online at http://www.kiwicareers.govt.nz/default.aspx?id0=3. This is a brilliant resource to send clients to so they can look requirements and training for any areas that spark their interest
• Career Quest – an interactive careers exploration CD Rom produced by Career Services. Read more about it at http://www.careers.govt.nz/OnlineShopping/Catalogue.asp?docid=220
• Myers Briggs Type indicator test information at http://www.cpp.com/products/mbti/index.asp
• 'Choices' software programme produced by Heather Carpenter of Careerworks. Contact her on heatherc@career.net.nz

Enjoy!

TLAs for SMEs

Here are this newsletter's TLAs for you:

• OGA, Office Genuine Advantage
• OVA, Office Validation Assistant
• WGA, Windows Genuine Advantage

Please feel free to email me with any TLAs that you want to get the bottom (meaning!) of.

Tips, Short+Hot Keys

In this & the next newsletter, we are looking at all you can do with Alt, Shift, Ctrl using plus (+):

• Access "Choose the selected data column for output (The check box next to the name of the column) 1If multiple items are selected, pressing this key affects all selected items. Select multiple items by holding down the SHIFT key while clicking them. Toggle the selected state of a single item by holding down CTRL while clicking it." + (Plus)
• PowerPoint "Stop or restart an automatic slide show" + (Plus)
• Word "Open the Address Book in the To field; works with keys for sending E-Mail" Alt & + (Plus)
• Word "Customize Keyboard Shortcut" Alt & Ctrl & + (Plus) NUM
• Word "Display Customize Keyboard dialog box and create a shortcut key for a menu command; use by typing shortcut key, selecting a menu command, and then adding, changing or removing desired shortcut key from within the dialog box" Alt & Ctrl & + (Plus)

Hot Linx

If you are currently looking at how to keep your vehicle running costs down, check out the government's website http://www.fuelsaver.govt.nz/

Want to understand spam? Then go to the How Stuff Works website for the lowdown at http://computer.howstuffworks.com/ref/spam.htm?cid=e14

The UK has plans to go wireless in twelve locations across Birmingham, Edinburgh, Leeds, Liverpool, Cardiff and Westminster. Read about it at http://www.pocket-lint.co.uk/news.php?newsId=3353

Apparently career women make healthier mothers, keep better health and avoid middle age spread. Check it out at http://www.abc.net.au/science/news/health/HealthRepublish_1638319.htm

---

                                Catch you again soon!! E-mail your suggestions to me here

read more "Newsletter Issue 114, May 2006"

Newsletter Issue 102, September 2005

Sam Young Newsletter

Issue 102, September 2005

Hi guys,

Are you in the market for a new PDA? Then check out palmOne's LifeDrive below.

Are your customers or employees satisfied? Don't know? Then start Asking the Questions. 

Excel's COUNTIF Function Part 2   TLAs for SMEs        Tips, Short & Hot Keys        Hot Linx

Don't forget, if you want to be taken off my mailing list, click here to send me a reply e-mail and I will remove your name.

palmOne's LifeDrive

The palmOne LifeDrive has just been awarded the Editor's Choice Best Professional Palm award by Pen Computing. This PDA has a button to landscape or portrait view, has a nice external speaker,  4GB storage, WiFi, well designed with brushed aluminium exterior, excellent software bundle, 4GB storage, bluetooth, is compatible with older Palm software, has internet access, 4GB storage... can play your mp3s, store your photos, your files; and did I mention that it has 4GB storage?

The folks at palmOne consider the LifeDrive a mobile manager, one of an entirely new class of handheld products. PC Magazine (at http://www.pcmag.com/article2/0,1895,1817008,00.asp?rsDis=PalmOne_LifeDrive-Page002-152114) says "it's a cool new device for the gadget fanatic who wants to do a bit of everything on a regular basis, whether play a movie, game, or music, pull up photos, do some tolerable Web surfing, view or work on the occasional Office document, or look up an address. It's the first device we've seen that can actually handle all these tasks".

"Boxy yet sleek, the LifeDrive looks like something designed by Porsche. It measures 4.8 by 2.9 by 0.7 inches, weighs 6.8 ounces, and accommodates a huge 320-by-480 screen and a 4GB Hitachi 3K4 Microdrive. It's not actually longer or wider than many other PDAs, but it's deeper. Holes on the back help vent the drive's heat; the LifeDrive gets a little warm, but never uncomfortably so.

"The LifeDrive is designed to hold all your portable files; we filled ours with 600 MB of MP3s, a full-length movie, and a flotilla of review notes, and it was still more than half empty (program files must live in 64MB of RAM, but that's plenty of room for Palm OS software, which is generally pretty compact). There's a slew of ways to get your files on and off the device, too: You can beam them over Bluetooth, drop them in via SD Card, share them using built-in 802.11b Wi-Fi, sync them from a PC with included software, or just plug the LifeDrive via USB 2.0 into any PC or Mac, where it becomes an external drive without you having to add any additional software.

"PalmOne marries the LifeDrive's massive storage to a very usable set of software. You'll find DataViz DocumentsToGo 7, still the best Microsoft Office document reader/editor suite on any handheld, loaded on the product. The LifeDrive also comes with Pocket Tunes, an MP3/WMA player that supports playlists and syncs with Windows Media Player 10; a basic built-in video player; and WiFile, a sleek interface for downloading files from SMB shared drives over Wi-Fi, which we used with both PCs and Macs. Speaking of Wi-Fi, palmOne's implementation is far smoother than Microsoft's in Windows Mobile 2003 SE—you can access any available network in a few clicks."

However, there are a few drawbacks too. Apparently in some of the cheaper deals, the LifeDrive is sold minus the docking cradle; meaning that in order to use it you have to buy a docking cradle as an extra. Also it apparently has a short battery life and there are some vague problems with system crashes (although I don't know how extensive this problem is and there are no clarifications that I can find on the net).

All in all, the new LifeDrive looks pretty good. Dick Smith's have it for $898 (incl GST) at http://www.dse.co.nz/cgi-bin/dse.storefront/4327d4370a304c44273fc0a87f9906c3/Product/View/V6167

 

Asking the Questions

Are your customers happy? Do they recommend your business to others? If there is a problem, is the problem resolved well, making your customers happy to purchase from you again? Are your staff happy? Have you ever asked any of these questions and analysed the results?

If not, then perhaps now is the time to think about it. Unless you actually ask the questions and analyse the results, you may be missing out on some wonderful new business opportunities.

Customer feedback about how useful it would be if they could get a coffee and a place to plug in their laptop while their car is being serviced, or that customers would like to pay you via internet banking, or that your helpline closes too early could change your USP and your marketing strategy.

Customers

You don't need to get too complicated about asking the questions either. You can keep the whole process quite simple and informal by asking some of your core customers if they have time to answer perhaps five questions over the phone. Then the three key things that you need to do are to formulate a very clear brief of what information you want to get, then to design some VERY clear questions to provide the information you seek, and to get someone independent to do the asking.

If you need more information about a particular customers' experience, the customer can always be asked if they would mind the surveyors getting back to them at a later date for additional information (in general, most people are very happy to help if you are careful not to waste their time).

You could also award a prize to the customer who gave you the most valuable feedback.

Employees

Remember too that your employees are your front-line, taking care of your customers. To keep them involved and active, you need to listen to their input, and show them that you appreciate their efforts. You can run something as simple as a suggestion box (either paper or email, depending on your technology). Award a small prize for the best suggestion each month, quarter or year. NB: you actually need to implement the best suggestions, else your employees will become disillusioned very quickly.

Additionally, it is relatively easy to hold regular focus groups to keep up with any new issues and suggestions where a range of employees can have input. You could also use the focus group to vote on the suggestions from the suggestions box.

And if any of you need a hand, feel free to give me a call!

Excel's COUNTIF Function Part 2

Following on from last time, the "criteria" part of COUNTIF also lets you count items using >,>=, <,<=, or <> (the latter is 'not equal to'). Using the example from last week, the formula =COUNTIF(A1:A5,">5") will return a value of 2.

In addition, COUNTIF can count text as well, for example to count the appearances of "cat" from A1 to A5 as shown below:

          Column A

1. cat
2. cow
3. dog
4. cat
5. cat

Use '=COUNTIF(A1:A5,"cat")'. The result is 3. NB: upper and lowercase letters are treated exactly the same so COUNTIF will treat 'cat', 'CAT' or 'Cat' equally. Using words or combinations of letters for criteria is simply alphabetical comparison, and follows the same rules - ie 'cow' is greater than 'cat' because alphabetically 'cow' comes after 'cat', and 'cats' is greater than 'cat' as it has an additional letter.

But wait, there's more! You have to just count whole words, you could find out how many names start with anything from W onwards:

          Column A

1. Aaron
2. Bertram
3. James
4. Kimberly
5. Mirriam
6. Ophelia
7. Simon
8. Travis
9. Wallace
10. Zachary

Use '=COUNTIF(A1:A10,">w")' would return a value of 2 since lines 5 through 10 all contain values that are 'greater than' the letter W. Additionally, your criteria in your list: you could use the formula '=COUNTIF(A1:A10,"<w")', which would return a value of 2.

Next time we will look at using dates and wildcards.

Thanks to Woody's Office Watch for the background for this series. View what the Office Watch team have to offer at http://office-watch.com/

TLAs for SMEs

Here are this newsletter's TLAs for you;

• BPCP, Business Process Contingency Plan. Or a Business Contingency Plan or a Disaster Recovery Plan.
• PGP, Pretty Good Privacy. This is a popular freeware program used to encrypt and decrypt e-mail over the Internet

Please feel free to email me with any TLAs that you want to get the bottom (meaning!) of.

Tips, Short+Hot Keys

Continuing our Outlook hot key list, this time we look at all you can do with the plain old alphabet keys with Alt, Shift, Ctrl. In this newsletter we take letters A to D;

• Outlook "Select all" Ctrl & A
• Outlook "Create new Appointment or open selected Appointment" Ctrl & Shift & A
• Outlook "Bold or unbold text or opens Background Synchronisation menu when a background synchronisation is in process" Ctrl & B
• Outlook "Open the Address Book" Ctrl & Shift & B
• Outlook "Close print preview or Accept when responding to an E-Mail schedule request" Alt & C
• Outlook "Copy" Ctrl & C
• Outlook "Decline when responding to an E-Mail schedule request" Alt & D
• Outlook "Create a new contact or open a selected contact" Ctrl & Shift & C
• Outlook "Delete" Ctrl & D
• Outlook "Dial" Ctrl & Shift & D

Hot Linx

If you are crazy enough to want to write your name in Elvish, for the "How To" guide, check out  http://www.starchamber.com/paracelsus/elvish/elvish-in-ten-minutes.html?GXHC_GX_jst=8258c07850ea6164

Check out how to make houses or offices more energy efficient at this joint-venture Australian government and construction industry site at http://www.greenhouse.gov.au/yourhome/

Want to know a bit more about AC Neilsen's work? Then go to http://www.acnielsen.co.nz/ or read their press releases at http://www.acnielsen.co.nz/news_archive.asp

Getting OOS from too much PC work? Then check out Chch's Niche Software's product "WorkPace" at http://www.workpace.com/HOME:401849.4068

---

                                Catch you again soon!! E-mail your suggestions to me here

read more "Newsletter Issue 102, September 2005"