Skills Level: Intermediate I recently had to complete a form in Excel where the ID numbers of a group of learners were required, as well as their age. I had a list of their ID numbers ready, but age is not something that you can store along with other records since it is not static data and obviously changes every year. Despising forms and admin in general, I looked for a shortcut to calculate the age with as little effort as possible, avoiding manual calculations at all cost.
3 Comments
Skills level: Intermediate I happened to come across the ISNUMBER and ISTEXT formula’s while looking for something else, and thought to myself, this is quite nifty… don’t know if I am ever going to use it, but cool formula. What the formula does is simply confirm whether the data contained in a cell is formatted as text (ISTEXT formula) or as a number (you guessed it, the ISNUMBER formula). Pretty cool, but why would I ever need to use it? Until the next day… A colleague asked for my help with a simple formula that just would not calculate. It kept on giving a #VALUE! error. I immediately thought of checking the formatting of the cell which contains the formula - it was formatted as a number, so no problems there, and then I continued to check the formatting of all the cells used in the calculation… Also not finding any issues. All the cells seemed to be appropriately formatted as a number, but the #VALUE! error remained. Then I remembered the ISNUMBER and the ISTEXT formula that I just learned about the day before, and decided to test it. And low and behold, one of the cells sneakily kept its formatting as text even though we changed it to number.
Skills level: Intermediate
Errors in spreadsheets can be quite annoying. Especially if it is actually not a mistake, but just as a result of the data input. One of the errors that I often come across is the #DIV/0! error when trying to divide by zero. Sometimes, when you export some or other report, it is inevitable that some of the lines will have zero values, which may result in errors when trying to perform certain calculations. You might want to take the errors out, especially if you need the result for further calculations. If you need to sum the calculated column of values for example, the result will also result in an error. Below is a sales report for the Corner Stationery Shop for the month of August. The report includes all the items available to customers (even if none were sold during the month), the total sales value, and the number of items sold in the period. Skills level: Beginner Have you ever wondered why dates are sometimes displayed as a random number? Usually (these days) it starts with a number four, and would be 5 numbers in total. It almost seems like dates are coded in some way. If you thought that, you are absolutely correct. Each and every date has a unique whole number assigned to it, called serial numbers. If you look at the image above, you will see that the numbers follow each other. 20 Aug 2016 is shown as number 42602 (if not formatted correctly as a date), the following day is 42603 and so on.
This property allows you to be able to perform calculations with dates in excel. Because a date is equal to a whole number, you can subtract dates from each other. For example, you may want to know how many days there are between the 20th of August and the 3rd of October - maybe your holiday starts then and you want to start counting down the days! Skills level: Advanced In this lesson, I will illustrate how to use the SUMIF formula. It is a very nifty formula that you can use when you need to sum only certain items according to specific criteria. Imagine for example that you have a fruit cart. You sell Apples, Oranges and Peaches. For every sale that you make, you enter the date of the sale, the type and number of fruits sold, the price per fruit, as well as the total sales neatly into an excel table. At the end of the month, you want to see how much sales you made per fruit, and also how many fruits you have sold of each. At the end of the month, you want to calculate how many apples, oranges, and peaches you have sold respectively, and how much money you have made with each fruit.
Next to my data set, I created a simple table for my totals as follows. |
Carine HoughHi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog. Categories
All
|