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 HOUR function in Excel returns a whole number between zero and 23 as an indication of the hour associated with a time value. Zero is the period from 12am - 1am and then increases in whole numbers through to the period from 11pm to 12am which will return 23. The HOUR function "Returns the hour as a number from 0 (12:00AM) to 23 (11:00PM)" The function only requires one argument, a cell or an input that includes a time element. This could be of the format dd/mm/yy hh:mm or just a time reference of hh:mm. In this example, I've created a list of random times which I'll then combine with the HOUR function to return an interfere number that indicates the time associated with the input. The formula is simply =Hour(D2) which I've then filled down to assess the time associated with each value in the range. As you can see from the example, the output of the function is an integer number that varies with each of the times in the source cells.
The VLOOKUP function in Excel provides some advanced functionality that allows the user to combine information from one file, table or sheet into another file, table or sheet. It is a relatively simple formula to use but it does have some elements that required understanding before it can be used with confidence. A common scenario where Vlookup is often used is one where information relating to a specific product, person, or maybe an activity exists in two places and needs to be combined. In this example, I'm going to use a pre-existing table to provide the values I'd like to 'lookup'. And then I'm going to create a VLOOKUP function that returns the height of the person when supplied with one of the 3 names in cell F3 You can see there are 3 mandatory arguments and an additional optional argument. The first argument, lookup_value, is the information that will be used to identify the associated match in the existing table, in this instance this will be the name which