Sample 1: Appraisal Form

Appraisal Form

Time to learn: 30 minutes

Functions Required:

1. =CONCATENATE
2. =IF
3. =OR

Let’s try using the formulas we know to create a spreadsheet of our own.

Let’s say that we have 5 staff, John, Jane, Dora, Peter and Sam. It’s the end of the year and we have to appraise their performance.

We can appraise their performance from 1 to 5 on 3 categories; Teamwork, Planning and Work execution, with 1 being ‘Poor’ and 5 being ‘Excellent’. Of course, we would like to write their appraisals in a sentence too.  So, here’s our appraisal form.  It’s all in text, so it’s easy to do.

Now, the fun part.

Keep in mind that the names of the staff John, Jane, Dora, Peter and Sam are contained in cells B2 to B6 respectively and that the words ‘Teamwork’, ‘Planning’ and ‘Work execution’ are contained in cells C1, D1 and E1 respectively. Remembering this will help to make sense of the formulas we are using.

Let’s create some sentences. We will do that by selecting certain cells and using the CONCATENATE and the IF and OR functions:

=CONCATENATE($B2,”‘s”,” “,C$1,” is “,(IF(OR(C2=1),”Poor.”,IF(OR(C2=2),”Below Average.”,IF(OR(C2=3),”Average.”,IF(OR(C2=4),”Good.”,IF(OR(C2=5),”Great.”,”unrated.”)))))))

We will start as usual, with a ‘=’ and then the CONCATENATE function. Remember that ‘(‘ and ‘)’ will need to frame the contents.

Now, what are those ‘$’ symbols in front of the cell references for B2 and C2?

‘$’ functions as an anchor, meaning that even if you copy cell formulas onto another cell, the letter (for column labels) or number (for row labels), remains the same. This is useful for when you want to make sure the cell that is referenced is always on the same column or same row.  You can do this by selecting the cell reference and pressing F4.  Pressing F4 repeatedly will cycle through the possible anchoring types.

This is better illustrated when you look at the formula just below it:

=CONCATENATE($B3,”‘s”,” “,C$1,” is “,(IF(OR(C3=1),”Poor.”,IF(OR(C3=2),”Below Average.”,IF(OR(C3=3),”Average.”,IF(OR(C3=4),”Good.”,IF(OR(C3=5),”Great.”,”unrated.”)))))))

Notice that the ‘$’ holds the column label B in place from B2 to B3 and from C2 to C3. That’s because the ‘$’ is in front of the letter or column label, hence the column reference remains unchanged even if the cell’s formulas are copied to another cell.

Now look at the formula just to the right:

=CONCATENATE($B2,”‘s”,” “,D$1,” is “,(IF(OR(D2=1),”Poor.”,IF(OR(D2=2),”Below Average.”,IF(OR(D2=3),”Average.”,IF(OR(D2=4),”Good.”,IF(OR(D2=5),”Great.”,”unrated.”)))))))

Notice that C$1 is now replaced by D$1. This is because the ‘$’ is in front of the number or row label, meaning that the column or letter may change when the cell formulas are copied but not the not the number or row.

Now, I only typed this formula once and not 15 times, as you might think. The reason I could do that, was because I had placed the ‘$’ symbols in front of the column or row references strategically to lock on to certain cells.

What does this formula do, then?

It uses the CONCATENATE function to add the following together:

Names of Staff: The names are contained in cells B2 to B6, so I made sure the ‘$’ symbol is in front of the column label ‘B’ of B2 to B6, so that wherever I copied the cell contents, the column reference ‘B’ containing the names of the staff (B2 to B6) always remains the same and doesn’t shift to C2 to C6 or D2 to D6.

Text: Now remember that if you want to CONCATENATE text, you have to frame the text in “”. So, by adding “’s”, we’ve added ‘s to all the names of the staff automatically (eg. John’s, Peter’s).  The same goes for the “ is “ that we’ve added.

Appraisal category: Notice that C$1 refers to the word ‘Teamwork’, D$1 refers to the work ‘Planning’ and E$1 refers to the words ‘Work execution’. These words are automatically inserted into the sentence because it is within the CONCATENATE function.

Now, how about the last bit:

(IF(OR($C2=1),”Poor.”,IF(OR($C2=2),”Below Average.”,IF(OR($C2=3),”Average.”,IF(OR($C2=4),”Good.”,IF(OR($C2=5),”Great.”,IF(OR($C2=0),”unrated.”)))))))

That does seem pretty daunting, doesn’t it? Well, this is actually just a repeated formula.  Remember the IF and OR functions?

Well, a basic IF and OR combination function looks like this:

= IF(OR($C2=1),”Poor.”,””)

This means that if cell C2 is equal to 1, the cell the formula is in will give you the value “Poor”. If the cell C2 is any other value, it will give you a blank, signified by the “” with nothing in it.

Now, by combining the first part of the IF(OR combination function, we can tell Excel to check if cell C2 is equal to 1 and if it is, to tell us “Poor”, and immediately after that, to check if C2 is equal to 2 and if it is, to tell us “Below Average” and so on. So, broken up into its component parts, the formula would look like this:

IF(OR($C2=1),”Poor.”,

IF(OR($C2=2),”Below Average.”,

IF(OR($C2=3),”Average.”,

IF(OR($C2=4),”Good.”,

IF(OR($C2=5),”Great.”,

IF(OR($C2=0),”unrated.”))))))

Looks a little bit less intimidating now, yeah?

The final part would be to combine the 3 sentences appraising the staff by their Teamwork, Planning and Work execution into one short paragraph. For this, we will just use the CONCATENATE function again like so:

=CONCATENATE(C9,” “,D9,” “,E9)

To show how this would then work, I am going to put numbers from 1 to 5 in the appraisal category boxes just to the right of each staff member’s name and you will see how the appraisal statement changes.

Now, you can do it too.