With Microsoft Excel it is possible to produce a list of records on accounts , invoices and qualifications, of the simplest, but also of a complex nature and with more possible functionalities; for this reason the program has become one of the main choices in the labor and financial field.
You can download Excel from its page together with the complete Microsoft Office package, its mobile version is also available or even Microsoft Office 365, the web version of all these software. Today we will teach you how to generate columns with accumulated values in Excel for your data.
Index( )
- How to create a total Accumulated Value in my Excel sheet?
- on Windows
- with mac
- What are the benefits of using calculated columns to get the accumulated values out of Excel?
- How to create a list of sequential dates in my Excel workbook?
How to create a total Accumulated Value in my Excel sheet?
An accumulated value is a series or numerical sequence in which a value is the result of the sum of the previous two, in the style of the Fibonacci series for example. These values can be made with any type of number, it is even possible to add or subtract several accumulated percentages ; these columns of accumulated values can be made for all records, taking into account a conditional or even taking into account several possible criteria and perform more elaborate calculations.
on Windows
Open your Microsoft Excel program on your PC and make a new spreadsheet, you must previously record all the data you are going to need and create the necessary list for it. It is important that everything is properly ordered, then you will create a separate column where the accumulated values will be made:
- Stand on the first cell of your column and type the function =SUM, which is the one we will use.
- Open parentheses and select the first cell of the values to which you are going to add the cumulative values, then “colon” and Excel will automatically copy the same cell you selected; it would look like this: B4:B4; This is an example.
- Now on the first coordinate of the cell, the one before the colon and press F4, until it looks like this “$B$4”.
- This will make the sum not scroll in the table, but stay, close parentheses and press “Enter”.
- The first value in the cell will appear, for the rest of the boxes, what you have to do is double click on the lower right corner of the cell or click and scroll down.
With this you would have already made your column of accumulated values , the formula would be like this =SUM($B$4:B4), remember that the coordinates are just examples, since it will depend on where you have placed the data.
with mac
The process on MacOS is similar to that on Windows, as Excel is also used as the main spreadsheet program here . In Excel it is also possible to convert from feet to inches with the formula =CONVERT().
The steps to follow will be the same as those explained in the previous section. However, there are two variants that we will explain to you, these can be very useful in specific cases:
- =SUM.IF(): this variant is to execute a sum taking into account ONE criterion (or conditional) if in your list you have to take into account some specific conditions.
- =SUM.IF.SET(): Like the previous one, it also adds taking into account the criteria, but this one can take into account as many conditions as you have in your list. For example, cumulative values, but from specific dates and to be restarted.
To use either of these two formulas, it is important that you have the conditions in a separate cell, since they will be important data that the formula will request and that you must enter in them.
What are the benefits of using calculated columns to get the accumulated values out of Excel?
The accumulated values are taken from other values that are already established, regardless of whether they are “random” numbers or numbers that have also resulted from other calculations; in other words, you can’t get a column of accumulated values , if you don’t have the previous data for it.
How to create a list of sequential dates in my Excel workbook?
It is also possible to change the name of a spreadsheet in Excel if you need to customize or more easily identify your work, in case you have several sheets open within the same file in the program.
In the event that you want to generate sequential dates, first of all it is important to place the conditions (or criteria , it is the same) that in this case it will be important, depending on the number of criteria you have, you will have to use =SUMAR.SI( ) if you have one or, on the other hand, if you have several, you use =SUMIF.CONDITIONS().
Then, the steps to follow will be practically the same as those described in the previous sections, but you will have to add the additional cells that represent the criteria so that you can create the cumulative value cells but with the sequential dates.
