How to Repeat a Formula with a Changing Value in One Cell in Excel

Sometimes, you may want to use the same formula multiple times in one cell, but with only one value changing each time. For example, you may want to sum up the values from different worksheets, or look up the values from different columns, or apply different functions to the same value. In this article, we will show you how to do this in Excel using different methods.

The basic idea is to use a symbol or a function that can concatenate or join multiple formulas together in one cell. In Excel, there are two common ways to do this:

• Using the ampersand (&) symbol: This symbol can join two or more text strings or formulas together. For example, ="Hello "&"World" will return Hello World.
• Using the CONCATENATE function: This function can join two or more text strings or formulas together. For example, =CONCATENATE ("Hello ","World") will return Hello World.

Using either of these methods, you can create a formula that repeats itself multiple times in one cell, but with only one value changing each time. The value that changes can be a cell reference, a number, a text, or a function argument.

Procedures

To use a formula multiple times in one cell with only one value changing, follow these steps:

1. Write the original formula that you want to use multiple times in one cell. For example, =SUM (A1:A10).
2. Identify the value that you want to change each time. For example, the column reference A.
3. Write the new value that you want to use instead of the original value. For example, B.
4. Use the ampersand (&) symbol or the CONCATENATE function to join the original formula and the new value together. For example, =SUM (A1:A10)&SUM (B1:B10).
5. Repeat steps 3 and 4 for as many times as you want. For example, =SUM (A1:A10)&SUM (B1:B10)&SUM (C1:C10).
6. Enter the formula in one cell and press Enter. You will see the results of each formula separated by a space. For example, 45 55 65.

Comprehensive Explanation

Let’s look at a more detailed example of using a formula multiple times in one cell with only one value changing. Suppose you have the following data in three worksheets named Project 1, Project 2, and Project 3:

You want to calculate the total cost of each project by multiplying the hours and the rate for each person, and then summing up the results. You also want to display the total cost of each project in one cell in a summary worksheet. How can you do this?

One way is to use the SUMPRODUCT function, which can multiply and sum the values in two or more arrays. For example, the formula =SUMPRODUCT ('Project 1'!B2:B6,'Project 1'!C2:C6) will return the total cost of Project 1, which is 1020+1525+1222+818+14*24 = 900.

However, if you want to use the same formula for Project 2 and Project 3, you need to change the worksheet name each time. For example, the formula for Project 2 would be =SUMPRODUCT ('Project 2'!B2:B6,'Project 2'!C2:C6), and the formula for Project 3 would be =SUMPRODUCT ('Project 3'!B2:B6,'Project 3'!C2:C6).

Instead of writing three separate formulas, you can use one formula that repeats itself multiple times in one cell, but with only the worksheet name changing each time. To do this, you can use the ampersand (&) symbol or the CONCATENATE function to join the formulas together. For example, the formula =SUMPRODUCT ('Project 1'!B2:B6,'Project 1'!C2:C6)&SUMPRODUCT ('Project 2'!B2:B6,'Project 2'!C2:C6)&SUMPRODUCT ('Project 3'!B2:B6,'Project 3'!C2:C6) will return the total cost of Project 1, Project 2, and Project 3 in one cell, separated by a space. The result would be 900 975 930.

Scenario and Example

Let’s look at another example of using a formula multiple times in one cell with only one value changing. Suppose you have the following data in a worksheet named Sales:

You want to calculate the average sales of each product for the first half of the year. You also want to display the average sales of each product in one cell in another worksheet named Summary. How can you do this?

One way is to use the AVERAGE function, which can calculate the average of a range of values. For example, the formula =AVERAGE (Sales!B2:B7) will return the average sales of Product A, which is (100+120+140+160+180+200)/6 = 150.

However, if you want to use the same formula for Product B and Product C, you need to change the column reference each time. For example, the formula for Product B would be =AVERAGE (Sales!C2:C7), and the formula for Product C would be =AVERAGE (Sales!D2:D7).

Instead of writing three separate formulas, you can use one formula that repeats itself multiple times in one cell, but with only the column reference changing each time. To do this, you can use the ampersand (&) symbol or the CONCATENATE function to join the formulas together. For example, the formula =AVERAGE (Sales!B2:B7)&AVERAGE (Sales!C2:C7)&AVERAGE (Sales!D2:D7) will return the average sales of Product A, Product B, and Product C in one cell, separated by a space. The result would be 150 225 300.

To make the result more readable, you can also add some text and formatting to the formula. For example, the formula ="Average sales of Product A: "&AVERAGE (Sales!B2:B7)&", Product B: "&AVERAGE (Sales!C2:C7)&", Product C: "&AVERAGE (Sales!D2:D7) will return the average sales of each product with labels and commas. The result would be Average sales of Product A: 150, Product B: 225, Product C: 300.

Excel Table

To illustrate the example above, we can use an Excel table to display the data and the formula. Here is how the table looks like:

The formula in the last row calculates the average sales of each product for the first half of the year. The result is:

To display the average sales of each product in one cell in another worksheet named Summary, we can use the following formula:

The result is:

There are other ways to use a formula multiple times in one cell with only one value changing in Excel. Here are some of them:

• Using the INDIRECT function: This function can return the value of a cell or a range of cells based on a text string that represents the cell or range address. For example, the formula =INDIRECT ("A1") will return the value of cell A1. You can use this function to change the value of a part of a formula by using a text string. For example, the formula =SUMPRODUCT (INDIRECT ("'Project "&A1&"'!B2:B6"),INDIRECT ("'Project "&A1&"'!C2:C6")) will return the total cost of the project whose name is in cell A1. If you change the value of cell A1 to 1, 2, or 3, the formula will return the total cost of Project 1, Project 2, or Project 3, respectively.
• Using the CHOOSE function: This function can return a value from a list of values based on an index number. For example, the formula =CHOOSE (2,"A","B","C") will return B, which is the second value in the list. You can use this function to change the value of a part of a formula by using an index number. For example, the formula =AVERAGE (CHOOSE (A1,Sales!B2:B7,Sales!C2:C7,Sales!D2:D7)) will return the average sales of the product whose number is in cell A1. If you change the value of cell A1 to 1, 2, or 3, the formula will return the average sales of Product A, Product B, or Product C, respectively.