1. Get the day of the Week from Date
where cell A1 contains the date 01/12/2009 the formula
= Text(A1,”ddd”) will give Tue
= Text(A1,”dddd”) will give Tuesday
2. Count Occurences in a Column
Sometimes you may wish to count the number of occurences of a number or text in a sequence of cells.
Using our example we see that
=CountIf(A1:A10,5)
will give the number of times the number 5 appears in the cells from A1 to A8. The result is 3.
=CountIf(B1:B8, “Paul”)
will give the number of times ‘Paul’ appears in the cells from B1 to B8. The result is 4.
3. Use IF to display incorrect values
If you want a spreadsheet to tell you when a value is incorrect you can use the ‘IF’ function.
In our example we compare the value in two cells and if they are equal we display “Correct” otherwise we display “Incorrect”.
1. condition: A2 = B2
2. if condition true: “Correct”
3. if condition false: “Incorrect”
Another example is =IF(A1>=40, “Pass”,”Fail”)
4. Get part of a text field
To take a part of a text field you can use the functions LEFT, RIGHT or MID. In our example below we can get the file extensions by selecting the three rightmost characters.
However for file extensions with just two letters this will not work so we need to be a bit more clever.
The following formula will return only the values after the “.”
=MID(A2,FIND(”.”,A2)+1,3)
where
1. A2 is the text to use
2. Find returns us the position to start – in this case the first character after the “.”
3. The value 3 the is number of characters to return. If there are less characters then it will not cause a problem.
Nice one Paul. I’m a capable excel user but seldom have the cause. However, I’m sad/geeky enougth to have a favourite key! F2 changed my life man !!
(f2 lets you edit the cell – none of the pesky mouse business)
Have you used conditional formatting? It’s really good especially in 2007.