Tag: Excel Database

Rounding in Microsoft Excel

How to Rounding Data in Microsoft Excel


The ROUND, ROUNDUP and ROUNDDOWN functions are useful when rounding numbers in Excel. These functions are categorize Math and trigonometry.

The ROUND function in Excel

We use the ROUND function to work with any number of decimal places. Unlike when we apply a format on the cell to display more or fewer decimal, the ROUND function effectively modifies the stored value in the Excel cell. The syntax for the ROUND function is: ROUND(number, num_digits)

The function only requires two arguments, the first of them the number we want to round, the second is the number of decimal places that we need. Consider the following examples: = ROUND (3.1416, 0). It returns the value 3, because we are indicating zero as the number of decimals we need. = ROUND (3.1416, 3). It returns the value 3,142 because we specified three decimal places.

ROUNDUP and ROUNDDOWN functions in Excel

These functions have the same arguments that the ROUND function and the difference is that ROUNDUP always rounds up the specified number and the ROUNDDOWN function makes rounding down.

In the next picture you can see the difference between each of the three functions by applying rounding the value of the mathematical constant PI.

Rounding Data Options in Excel

Each function applies a different rounding even if you applied the same number of decimal places. Notice how the result of the function ROUND sometimes agrees with the result and sometimes ROUNDUP agrees with the result ROUNDDOWN. The next time you need round in Excel know that there are three functions that can help you get the desired result. (Source: Excel Total)


How to Edit & Delete Data in Excel

How to Edit & Delete Data in Excel




Edit Cell Data: Once you enter text, a number, a date, or a time into a cell, that cell data is not set in stone.If the data you entered into a cell has changed or is incorrect, you can edit the data to the updated or correct value. You can edit cell data either directly in the cell or by using the Formula bar.

Delete Data from a Cell: If your worksheet has a cell that contains data you no longer need, you can delete that data. This helps to reduce worksheet clutter and makes your worksheet easier to read.



Two Variable Data Table in Excel

Two Variables Data Table in Excel



In a previous article I showed the benefits of the data tables (Data Table in Microsoft Excel) as an option of What-If analysis. This time we review a classic example of the use of a table of data for analysis of two variables.


The scenario is as follows. Suppose I want to apply for a personal loan from a bank, I’m doing a research with several banks and each of them has given me an interest rate as well as several different payment terms. The first case that I investigated is:

Two Variables Data Table Initial Data

Two Variables Data Table Instead of making the calculation for each option interest rate and term, adequately I will accommodate the data to form a data table. In the rows I will specify different rates of interest and in the columns possible terms.

Rows and Columns for the Data Table


To create the data table with two variables I will select the range B4: G11 and I go to the Data tab and click the “What-If” button to select whether the “Data Table” option. Within the dialog I must choose each of the input cells for the data table as follows:

What-If Data Table Dialog Box

When click OK the data table is generated

Two Variable Data Table Results

In this data table can observe what we expected, the higher is the term the monthly payments are reduced and the greater is the interest rate your monthly payment increases. With this information I can properly analyze and choose the option that best fits my chances of monthly payment. (Source: Excel Total)



How to Use Subtotals in Microsoft Excel

How to Use Subtotals in Microsoft Excel


When you have a data table with a lot of information, subtotals in Excel can help us understand and interpret the information. Excel lets you add subtotals in a very simple way.

Suppose the following data table where I want to subtotal sales for each month:

Subtotals in Excel Initial Data

The first thing to do is sort the data by the column on which the subtotals are going to be obtained. For this example I will order the data for the “Month” column. To do this you have to click any cell and then choose the Sort Command in the Data Tab. Finally click in Descendent command. (In Windows click in Sort from older to newer).

Sort a Table Button

The table is sorted

Data Sorted for Use Subtotals

The Subtotals Button in Excel

For insertion of the subtotals in Excel we must press the Subtotal command from the Data tab.
Excel shows the Subtotals dialog box

Subtotals Dialog Box in Excel

Now we are going to explain each box. The first one is At each Change in: we are going to select Month, that means that every change in the column month excel is going to put the subtotal. The second is the Function, in this example we are going to use Sum function. Then select the Sales item in the Add subtotal to menu, this means that excel is going to use the function with the date of the selected column. Finally click OK and excel calculate the subtotals:

Table with Subtotals Added in Microsoft Excel

Notice how Excel has inserted a new row containing the subtotal for each month. In addition to the left of the spreadsheet Excel places additional controls are useful to hide or display groups of data according to the subtotals. Clicking on any of them the corresponding group expands or contracts:

Subtotals Groups in Microsoft Excel


Data Table What-If Analysis in Excel

Creating a Data Table in Microsoft Excel


Data tables are part of the What If analysis tools that allow us to change the value of some cells to see how it affected the original result. A data table analyzes a set of values and determines possible outcomes.

Data table of one variable

The simplest example of a data table is one that uses a variable for calculations. Assume the following scenario:

Data Table What-If Analysis Initial Data

In this example I’m making a sales forecast for 2015 based on 2014 sales and expecting a growth rate of 3.2%. What I want to do is to know what the sales projection for 2015 if the growth rate was different. For this test I will place the growth rates that will be used as follows:

Growth Rates Estimation with Data Table What If Analysis

To create the data table I select the range of cells as shown in the previous image and then go to the Data tab, and in the Analysis group press the What-If button finally select the Data Table option.

Data Table What-If Analysis Button

The Table Data dialog box is displayed and in the text box Column Input Cell you must select the cell B2 that is the cell that contains the percentage of growth.

Data Table What-If Analysis Column Input Cell

Clicking the OK button, adjacent cells will be filled with growth rates projected value corresponding to each of the sales rates.

Data Table What-If Analysis Growth Estimations

Excel has created the data table in the range A4: B12 and thus can analyse different sales projections for different growth rate. Once you’ve finished analysing the information, if I try to remove some of the cells belonging to the range of the data table, Excel will display a warning message that you cannot change part of a data table. If you want to delete the data table you must first select the entire range before pressing the delete key. (Source: Excel Total)