When presented with a table of data that requires analysis, one common requirement is to calculate a running total or what some might call a cumulative sum of individual values. One application might be a table containing the amount of money spent across a number of transactions and there is a need to provide a running total to show the total spend across the data set. There are a couple of different methods to achieve this but the easiest way is to use the function =sum($B$2:B2) where column B contains the values that you wish to add in a running total. This gif (below) is the above function being added to a table containing values. I've used the keyboard shortcut F4 to set the cell reference to absolute so that when it fills down, the second part of the cell range increments with each row.
Posts
The COUNTIF function provides the ability to establish how many occurrences of a specific value or condition exist in a range of values. This function begins to increase capability with a function that is simple to operate. COUNTIF "Counts the number of cells within a range that meet the given condition" There are two arguments required to use the COUNTIF function, the first, range, is usually a group of cells, the second, criteria, is the argument that is used to match the cells in the range. In this example, we'll use the COUNTIF function to establish how many time the word "day" appears in a list of text. The range of cells that need to be checked for the word "day" exist in the range B2:B28 and the criteria we wish to count is entered as "day". The result of this function is the following: So we can conclude there are 7 occurrences of the word "day" in the range B2:B38. We can also use a cell reference as the second argument as
When working with data tables and formulas, its common to come across errors. There are a number of ways of dealing with errors one of which is to use the IFERROR formula to identify an error and deal with it in a controlled manner. To demonstrate the application of this formula we'll need to create an error. The simplest way to do this is with the formula =1/0 which will generate the #DIV/0 error as shown here. The IFERROR function has two arguments, both mandatory. The first, value, is the part of the function that will be tested to see if it returns an error. The second argument, value_if_error, is the argument that is returned if the value returns an error. For example, if we set the first argument, value, to 1/0 and the second argument, value_if_error to "Error", the formula will return "Error" because 1/0 returns the #DIV/0 error as described earlier in the article. returns...
When working with logical tests in Excel, there is occasionally the need to test the contents of a cell to establish if it is blank or not, to do this, the ISBLANK function will return a suitable response. The ISBLANK function requires just one argument, value, which is the argument that is tested to establish if it is blank or not. If the argument supplied is blank then the returned value is TRUE, if the argument supplied is not blank then the response is FALSE. In this example, the function is supplied a range of values, including blank and non-blank values and the response is shown below.