3 Common Excel Formulas
If you have worked for at least a few hours with Excel at work or at home, by now you are probably comfortable copy/pasting data, entering simple formulas, and making basic formatting changes like adding bold or italics to text.
The great thing about Excel — and this goes for almost any user at any level of expertise — is that there is so much to know and learn that would be useful in making your life easier when it comes to crunching data. Learning these tricks and tips can help you to not only lighten your workload, but to help you find additional insights into the data-related projects you are working on.
One of the places I suggest that you start advancing your knowledge of Excel is to delve further into the world of formulas and functions. Functions are basically «ready-made» formulas that do lots of neat and very useful things.
Similarly, formulas entail combinations of mathematical operators, numbers, cell addresses, and sometimes functions themselves. Formulas are needed when there is no specific existing function for what you need to do, or for when you need to combine multiple functions together.
Here are 3 common Excel formulas that you should definitely learn:
1. Remove unwanted spaces at the beginning or ending of a string of text:
Okay, let’s start with a quick and easy one. If you are dealing with large amounts of text-based data (e.g., addresses, names, etc.) and — especially if it was collected from various sources or hastily entered via manual data entry — it is likely that there are lots of superfluous spaces before and/or after many of the words or phrases. These spaces may be invisible to the naked eye, but they can represent an annoying problem if you want to, for example, sort the data properly, use it in graphs, or combine it.
The solution? Use the TRIM() function. For example:
=TRIM(A3) (note: this would remove any unneeded spaces before or after the text in cell A3).
Important: this function successfully removes the extra spaces at the beginning and end of a string of text, but it does not touch the spaces between bits of text. So, for example, the name «John Smith» will NOT become «JohnSmith» when using TRIM(). If you want to remove those spaces, used Excel’s Replace feature (replace the space with nothing).
2. Calculate the amount of time between two dates:
If you have been using Excel for a while, you may have been faced with the need to calculate the time difference (in days, weeks, months, or years) between two different dates. The formula is pretty straightforward. The formulas for days, months and years are almost identical, and are as follows:
=DATEDIF(A1,B1,»d») (this calculates the number of days between the two dates in cells A1 and B1)
=DATEDIF(A1,B1,»m») (this one calculates the number of months)
=DATEDIF(A1,B1,»y») (meanwhile, this calculates the number of years)
Finally, the formula for calculating the number of weeks between two dates looks like this:
=INT((B1-A1)/7) (where the dates are entered in cells B1 and A1)
3. Find the smallest or largest number in a range:
Finally, you may have situations arise whereby you are faced with a large number of cells grouped together (called a range of cells) and you want to know which is the largest and which is the smallest number among them. One solution to this would be to just sort a column and figure it out that way. But, a simple sort will not do the trick if you have a range that spans multiple columns. The solution? Use MIN and MAX functions. Here are two examples:
=MIN(A1:D3) (note: this finds the smallest number among the numbers in cells A1 through D3)
=MAX(A1:D3) (note: this finds the largest number among the numbers in cells A1 through D3)
Excel is capable of processing a very large number of functions and formulas which, if you choose to take a bit of time to master, will speed up your work tremendously. And, doing so will likely reduce the chances for making errors when compared to trying your own, homemade workaround methods.
Have a small, medium or large-size Excel data project on your hands? We can help! Our work is accurate, and we offer quick turnarounds and very reasonable prices. Check out: http://www.jcjinteractive.com/services/database-marketing/ for more info.
More Мейзу МХ3 Articles