Sometimes, you may want to change the input values of a formula without modifying the original data. For example, you may want to convert the units of measurement, apply a discount rate, or adjust the inflation rate. In this article, you will learn how to use different methods to change input values with a formula in Excel.
A formula in Excel is an expression that calculates values in a cell or a range of cells. A formula can contain constants, references, operators, and functions. For example, the formula =A1+A2 adds up the values in cells A1 and A2.
To change the input values of a formula, you can use one of the following methods:
- Use arithmetic operators to perform calculations on the input values. For example, the formula
=A1/100divides the value in cell A1 by 100. - Use cell references to link the input values to another cell or range of cells. For example, the formula
=A1*B1multiplies the value in cell A1 by the value in cell B1. - Use functions to perform complex calculations or operations on the input values. For example, the formula
=SUM (A1:A10)adds up the values in the range A1:A10.
Procedures
To change the input values with a formula in Excel, follow these steps:
- Select a cell where you want to enter the formula.
- Type the equal sign (=) to start the formula.
- Type the formula that contains the input values that you want to change. You can use constants, references, operators, and functions as needed.
- Press Enter to complete the formula and see the result.
Example
Suppose you have a table that shows the sales of different products in different regions, as shown below:
| Product | Region | Sales |
|---|---|---|
| A | North | 500 |
| A | South | 400 |
| A | East | 300 |
| A | West | 200 |
| B | North | 600 |
| B | South | 500 |
| B | East | 400 |
| B | West | 300 |
| C | North | 700 |
| C | South | 600 |
| C | East | 500 |
| C | West | 400 |
You want to change the input values of the sales by applying a 10% discount rate. To do this, you can use the following formula:
=C2*0.9
This formula multiplies the value in cell C2 by 0.9, which is equivalent to subtracting 10% from the original value. You can copy this formula to the rest of the column to apply the discount rate to all the sales.
The result is shown below:
| Product | Region | Sales | Discounted Sales |
|---|---|---|---|
| A | North | 500 | 450 |
| A | South | 400 | 360 |
| A | East | 300 | 270 |
| A | West | 200 | 180 |
| B | North | 600 | 540 |
| B | South | 500 | 450 |
| B | East | 400 | 360 |
| B | West | 300 | 270 |
| C | North | 700 | 630 |
| C | South | 600 | 540 |
| C | East | 500 | 450 |
| C | West | 400 | 360 |
Other Approaches
There are other ways to change the input values with a formula in Excel. Here are some examples:
- Use the INDIRECT function to dynamically change the input cell based on the value in another cell. For example, the formula
=INDIRECT ("A"&C1)returns the value in column A and the row number specified in cell C1. - Use the REPLACE function to replace a part of the input value with another value. For example, the formula
=REPLACE (A1,1,1,"B")replaces the first character of the value in cell A1 with “B”. - Use the IF function to change the input value based on a condition. For example, the formula
=IF (A1>100,A1*0.9,A1)applies a 10% discount rate to the value in cell A1 if it is greater than 100, otherwise returns the original value.
