Excel - Counting Occurrence of Text Values

Here's a poser I was asked today. Someone had an existing spreadsheet that had a column containing text, but she wanted a count of the occurrence of certain values.<br /><br />Let's look at a simple example of what she wanted.<br /><br /><a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/SYBotwpmFnI/AAAAAAAAD_k/vkg0aXO9mt0/s1600-h/20090128a.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5296348297150469746" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/SYBotwpmFnI/AAAAAAAAD_k/vkg0aXO9mt0/s400/20090128a.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 253px;" /></a>A summary of how many fields contain "red", how many "blue" and how many "yellow" is required. Now I'm going to keep this simple but if it was possible to enter either "red" or "RED" or "Red" or, worse, "scarlet" then the solution would be a bit more complicated, though still possible - perhaps a future entry to come!<br /><br />Here though the users have been told to use just the standard term, using lower case throughout. So the field only contains one of three possible values with a fourth value perhaps being an empty field.<br /><br />Here's a simple solution. There are other solutions more complicated but more elegant in that they don't require the use of any extra cells in the spreadsheet. Again perhaps a future entry will look at these (there's more than one way to skin this particular cat as there are in most forms of computing!)<br /><br /><a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SYBpk5qtyeI/AAAAAAAAD_s/qDvUMzJJsNw/s1600-h/20090128b.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5296349244463892962" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SYBpk5qtyeI/AAAAAAAAD_s/qDvUMzJJsNw/s400/20090128b.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 253px;" /></a>I've used the next three columns to calculate a numerical value registering the instances of each colour - a column being required for each of the three colours. In other words, I've not entered all those zeros and ones, the spreadsheet has used a formula to determine what value should be in each cell.<br /><br />The solution uses the <em>if</em> statement which follows this syntax form<br /><br /><strong>=if(argument being tested, value if true, value if false)</strong> <br /><br />The equals sign ( = ) is necessary before the <em>if</em> so that Excel recognises that what follows is a formula and not a piece of text.<br /><br />The argument being tested here is <em> does the value of cell A2 equal the text within the quotes - ie</em> red<em>?</em><br /><br />It does so the argument is true, therefore the <em>value if true</em> part of the formula is used which in this case is a numerical 1. Note that if we had wanted a text value we could have used quotes and text messagea as the <em>value if true</em> and <em>value if false</em> <br /><br />e.g.: <strong>=if(A2="red","A red lover!", "What's wrong with red?")</strong><br /><br />I needed to type in the formula for the first row only. After that I selected those three cells and used the <a href="http://computerbasicsforfree.blogspot.com/2008/11/click-and-drag.html">click and drag</a> technique to copy them down the columns. Excel is clever enough to know that if you copy in this way it will amend the formula for each row to calculate from <em>that</em> row - instead of every row calculating using the value in the first row.<br /><br />All we need to do now is add up each column for our summary. We could put a count at the bottom of each column and then use that or we could use the <em>sum</em> formula like this:<br /><br /><a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/SYBtRB-F9WI/AAAAAAAAD_0/aCgiYV1bsd4/s1600-h/20090128c.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5296353301141779810" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/SYBtRB-F9WI/AAAAAAAAD_0/aCgiYV1bsd4/s400/20090128c.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 379px;" /></a>And the job's a good 'un!

Diposting oleh Abdullah Syafii

Microsoft Word - Indenting Text

Many people are not aware that Microsoft's <em>Word</em> has a tool to help you indent text easily and neatly without struggling to decide where to end lines, inserting tabs etc.<br /><br />One of the most useful toggle buttons in <em>Word</em> is this one.<br /><br /><a href="http://2.bp.blogspot.com/_i9xWjDR4D-E/SUjVCosReAI/AAAAAAAADxQ/mVuqOO8axuE/s1600-h/20081217a.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5280704804351473666" src="http://2.bp.blogspot.com/_i9xWjDR4D-E/SUjVCosReAI/AAAAAAAADxQ/mVuqOO8axuE/s320/20081217a.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 240px;" /></a>It causes lots of unwanted symbols to appear all over your dicument but - <em>relax</em> - they won't be printed and they are there to help you. <br /><br />The strange character that is the same as that on the button shows where a paragraph ends. You have pressed the <em>Return</em> key at that point.<br /><br />Tabs are shown as a right-facing arrow.<br /><br />Spaces are represented by a dot, that can look a little (a lot, let's be honest!) like a decimal point.<br /><br />All these symbols disappear from the screen if you click the button again.<br /><br />Ok so let's have a look at a document where the author wanted to indent text but didn't know the <em>Word</em> tool for doing it. The tab key has been used instead to indent the text but now this needs doing on every single line - using our button we can see the tabs as right-arrows.<br /><br /><a href="http://2.bp.blogspot.com/_i9xWjDR4D-E/SUjWiQyiDxI/AAAAAAAADxY/1UXJK6Cws44/s1600-h/20081217b.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5280706447202717458" src="http://2.bp.blogspot.com/_i9xWjDR4D-E/SUjWiQyiDxI/AAAAAAAADxY/1UXJK6Cws44/s320/20081217b.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 240px;" /></a>This takes ages and is really messy if you decide you want to amend some text. Normally this means you have to delete all the tabs and start again for that paragraph.<br /><br />Let's start by removing all the tabs so the paragraph is not indented at all.<br /><br /><a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SUjXjBJwCSI/AAAAAAAADxg/73ml3XDDgfY/s1600-h/20081217c.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5280707559696632098" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SUjXjBJwCSI/AAAAAAAADxg/73ml3XDDgfY/s320/20081217c.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 240px;" /></a>Now we can click the <em>Indent</em> button that you should find (no matter what version of <em>Word</em> you use) on the main toolbar. Before clicking it you should make sure that the cursor is somewhere - anywhere - within the paragraph that you want to indent. You can make sure it is by clicking the left mouse button whilst the mouse pointer is over the paragraph.<br /><br /><a href="http://2.bp.blogspot.com/_i9xWjDR4D-E/SUjYrJZ_JuI/AAAAAAAADxo/Gk3qn5o0Zb8/s1600-h/20081217d.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5280708798862796514" src="http://2.bp.blogspot.com/_i9xWjDR4D-E/SUjYrJZ_JuI/AAAAAAAADxo/Gk3qn5o0Zb8/s320/20081217d.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 240px;" /></a>If you have an earlier or later (doesn't exist at the time of writing!) version of Word the layout of icons may look a little different but the symbol on the button should be exactly the same.<br /><br />Note that the button just to the left of the one I have pointed to does exactly the opposite - it will remove one level of indentation.<br /><br />Clicking the button I am pointing to indents the entire paragraph - all lines - by one tab stop. You can click it more than once to indent the text further.<br /><br />Clicking it once will make the text look like this:<br /><br /><a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/SUjZ2ItpyZI/AAAAAAAADxw/_GNO1sKP8vk/s1600-h/20081217e.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5280710087167035794" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/SUjZ2ItpyZI/AAAAAAAADxw/_GNO1sKP8vk/s320/20081217e.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 240px;" /></a>You can see that there are no right arrow symbols so there are no tabs inserted automatically.<br /><br />Instead <em>Word</em> has changed the properties of the paragraph to indent by a specified amount from the left margin.<br /><br />We can see this if we look at the paragraph's properties. The easiest way to do this is place the mouse over the paragraph, then <a href="http://computerbasicsforfree.blogspot.com/2008/11/just-right-click.html">right-click</a> and choose the option <em>Paragraph</em>.<br /><br /><a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SUjbHM30HAI/AAAAAAAADx4/v13esignqkQ/s1600-h/20081217f.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5280711479852801026" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SUjbHM30HAI/AAAAAAAADx4/v13esignqkQ/s320/20081217f.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 230px;" /></a>You will now see this dialogue box and I've again highlighted the relevant part. You can see that you could open this dialogue box and amend the distance the text is indented from the margin. You can also indent it from the right margin if you wish.<br /><br />Happy indenting!

Diposting oleh Abdullah Syafii

Click and Drag

One of the most important techniques to master on computers is the <em>click and drag </em>technique.<br /><br />This is used to move files or copy them to different folders or disk drives, to size boxes and shapes, for instance when creating a box to hold text in a document or when sizing a photograph imported into a page of text.<br /><br />It is also used extensively in spreadsheets to create a range of numbers or to apply a format or style to a number of cells in the spreadsheet.<br /><br /><a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/SR597pwl6PI/AAAAAAAADs0/EwKm7aF_-yI/s1600-h/20081115a.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5268787077845477618" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/SR597pwl6PI/AAAAAAAADs0/EwKm7aF_-yI/s320/20081115a.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 240px;" /></a><br /><br />It can also be used on the <em>Desktop</em> to arrange the icons how you want them.<br /><br />That's what I've done in the picture - I've used <em>click and drag </em>to move the Picasa icon to the right. This shows both before and after for illustration. Moving an icon in this way on the Desktop would not create a copy.<br /><br />How to <em>click and drag</em>? First point the mouse (move the mouse until the pointer is over the icon or file to be moved, then press and hold down the left button and without releasing the button move the mouse until the pointer is where you want the icon or file to be. Now release the mouse button and the movement will be completed.

Diposting oleh Abdullah Syafii

Just Right-Click...

This bewildering phrase is often first heard over a telephone when a new user has phoned someone for help.<br /><br />It is almost always chucked at you without any explanation at all and most new users hear it as "<em>Just write 'click'</em>". I'll give you the joke at the end but for now, here is what it really means!<br /><br /><a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/SRxZwEJ1l-I/AAAAAAAADsU/24mTqVrG0Bc/s1600-h/20081113e.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5268184346400233442" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/SRxZwEJ1l-I/AAAAAAAADsU/24mTqVrG0Bc/s320/20081113e.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 247px;" /></a><br />Your mouse - whether it has a wire or not, whether it has a red light underneath or a marble-sized ball that it rolls on, will have at least two buttons - a left and a right.<br /><br />It may have a middle button as well, which we can ignore for the moment, or a wheel as the two mice shown here do, which we can also ignore for the moment.<br /><br />The buttons are designated <em>left</em> and <em>right</em> and refer to the mouse being viewed from this angle - which is how it fits nicely into the hand. It is possible to switch the two buttons around if you are left-handed but let's not complicate things at this stage. So for the majority of people the phrase "<em>right-click</em>" refers to clicking the button which is marked on the photo by the pointing hands.<br /><br />What it will do is that it will take whatever the mouse pointer is hovering over on the screen at the moment at which it is clicked and give either more information or a list of options. It normally opens a separate window containing this information or list of options.<br /><br />Let's look at two examples from the same computer screen - the desktop; the screen seen when the computer first boots up. In the first example I have pointed the mouse pointer at a blank bit of the screen and then <em>right-clicked </em>- clicked the right mouse button once.<br /><br /><a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SRxfvm4aPkI/AAAAAAAADsc/kapuk5pLwdw/s1600-h/20081113f.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5268190935612276290" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SRxfvm4aPkI/AAAAAAAADsc/kapuk5pLwdw/s320/20081113f.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 209px;" /></a><br />This has caused a window to open up with a list of options that the user can choose from. You can release the right button if you have held it down - the window will not disappear until you click one of the mouse buttons again, or press the <em>Esc</em> button on the keyboard which you can do to close the window if you right-clicked by mistake.<br /><br />Any option which has a black triangle pointing right on the far right hand side means that clicking that option (with the <em>left</em> button) will cause a list of sub-options to appear. There are four such triangles shown above.<br /><br />Now compare that image with the next, where I have pointed the mouse (pointer) over one of the program icons on the desktop before right-clicking.<br /><br /><a href="http://2.bp.blogspot.com/_i9xWjDR4D-E/SRxgsIGXHPI/AAAAAAAADsk/KLGjRz0wJ7o/s1600-h/20081113g.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5268191975321312498" src="http://2.bp.blogspot.com/_i9xWjDR4D-E/SRxgsIGXHPI/AAAAAAAADsk/KLGjRz0wJ7o/s320/20081113g.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 209px;" /></a><br />The options are different. This is because they are options for a different context; the first one for the desktop space and the second for the specific icon that I was pointing to. Right-clicking a different icon may well have given a different list of options again.<br /><br />Here's a calming truth for the fearful. <em>You cannot do any harm </em>whatsoever by right-clicking and just having a look! There is often an option labelled "<em>Properties</em>". This often allows the user to choose their own preferences or set defaults for a program. Again no harm can be done by having a look!<br /><br />So... If you call a help desk and they say "Can you just right-click..." this is what they mean. They don't mean "write click". So the helpdesk joke much loved by techies goes...<br /><br />A user phones up with a problem and the technician says "<em>Can you right-click?</em>" <br />The user says "<em>Yes I've done that</em>." <br />The technician says "<em>What happened?</em>"<br />The user says "<em>Nothing</em>..."<br />The technician assumes the user didn't do it right and says "<em>Can you right-click again?</em>"<br />The user says "<em>Ok, I've done that</em>."<br />The technician says "<em>What happened?</em>"<br />The user says "<em>Nothing</em>..."<br />The technician says "<em>So what's on your screen?</em>"<br />The user reads out "<em>Click click</em>..."

Diposting oleh Abdullah Syafii

Seeing What's On The Computer

Often when people get a computer for the first time, the sheer amount of "stuff" they need to know can put them off.<br /><br />One of the very basic things is <em>how can I see the content of my computer?</em><br /><br />Computers store <em>stuff</em> as files on the disk drive. The <em>stuff</em> includes the programs such as Windows itself, the software - the programs you can use to do things, and the things that you have done, for example a letter or list that you may have created using a word processor.<br /><br />A file typically has a name and an extension - the last part of the name following a dot or full stop in the filename. e.g. <em>eggs benedict recipe.doc</em><br /><br />These files are arranged and stored in folders - a bit like files in a filing cabinet.<br /><br />You can see the folders and the files using a piece of software (a program!) called <em>Explorer</em>.<br /><br /><a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SRwws6KvuWI/AAAAAAAADr8/ZtRHXneLM0A/s1600-h/20081113b.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5268139212203342178" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SRwws6KvuWI/AAAAAAAADr8/ZtRHXneLM0A/s320/20081113b.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 230px; height: 195px;" /></a><br />You may have these two icons on your <em>desktop</em>. That's the name for the screen that appears when your computer is switched on and has finished all its preparation tasks (also known as "<em>booting up</em>").<br /><br />Your own user name would appear instead of my name of course! Or if your computer is an older one it may have an icon with the label "<em>My Documents</em>"<br /><br />Double-clicking (clicking the mouse button twice in quick succession - the left button by default) either of these icons opens a piece of software (a program) called <em>Explorer</em> but each one has a particular opening point or view.<br /><br />Opening (double-clicking) <em>Computer</em> opens with a view of the disk drives, any CD or DVD drives and other storage devices. On my computer it looks like this, but don't worry if it looks slightly different or even a lot different on yours!<br /><br /><a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SRwyfU7J5AI/AAAAAAAADsE/C1RczBjT3J4/s1600-h/20081113c.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5268141177890792450" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SRwyfU7J5AI/AAAAAAAADsE/C1RczBjT3J4/s320/20081113c.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 241px;" /></a><br />In the left hand column there should be a shaded entry which shows which of the listed items is being shown in the right hand column - the greater part of the <em>window</em> or screen.<br /><br />In other words, the contents of the shaded item on the left are shown in the right hand side of the window. You can see the contents of any of the other items in the left hand list by moving the mouse pointer over them and clicking the button once.<br /><br />You will probably be able to see an icon in the left hand column labelled either with your name or <em>My Documents</em>.<br /><br />Double-clicking that would change the right hand part of the screen to show the same view as if you had opened that icon on your desktop instead of the <em>Computer</em> icon. So it doesn't matter which one you open as you can get to the other view anyway!<br /><br />The <em>My Documents </em>or <em>your name </em>icon shows the folders and files that either Windows has created for you by default or that you (and possibly other users if you share the PC) have created. If you and other users have to <em>login</em> (enter a name and perhaps a password) before the computer will boot up then you will only see your own files, not anyone else's. Likewise anyone else will not be able to see yours.<br /><br /><a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SRw1-VYnzTI/AAAAAAAADsM/RugS-bivRMI/s1600-h/20081113d.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5268145009125215538" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SRw1-VYnzTI/AAAAAAAADsM/RugS-bivRMI/s320/20081113d.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 162px;" /></a><br />Here are the folders as shown when I open the icon with <em>my name</em>.<br /><br />In Windows Vista they are called <em>Documents, Downloads, Music, Pictures, Videos </em>etc. In older version of Windows the <em>My Documents </em>folder contains the equivalent of some of the others: <em>My Music, My Pictures, My Videos</em>.<br /><br />Double-clicking one of those in turn opens the folder so you can view the contents of it. When you see a file rather than a folder (folders don't normally have an extension to the filename and files normally have a 3 or 4-character extension following a dot), double-clicking a file causes that file to open using whatever program it needs automatically.<br /><br />Whenever you create a new file by saving your work from a program, Windows opens a similar view of your files and folders so you can move into the correct folder to save similar files together.<br /><br />Don't worry if you don't see anything with a dot and extension because by default Windows hides these from you so you only see the name part of the full filename. The icon or small picture normally gives you a clue. Folders have a picture that looks like an old-fashioned paper folder from a filing cabinet.<br /><br />Explorer is a very useful program. It lets you create new folders, copy, rename and delete files and folders. We'll look at some of these features in a later entry.

Diposting oleh Abdullah Syafii

Why a Computer Basics Blog?

Hi all,<br /><br />The idea for having a blog that covered some basic computer information came from a number of friends and colleagues who were struggling to do simple things or get to grips with software such as Microsoft Office or my own observations of people doing things laboriously because they obviously didn't know the shortcut.<br /><br />I'm not putting myself up as a guru here - all I want to do is share what I know.<br /><br />I'm not making any promises that I'll cover what you are looking for or that I can help with specific problems.<br /><br />If this blog helps you (and it won't until I make at least one more entry...) then please leave a comment or tell a friend or colleague about it!<br /><br />John

Diposting oleh Abdullah Syafii