How to calculate the target value backwards with Excel formula

Key points of this article

When the result is known and only one input is missing, try to write the formula in reverse first, so that you can directly calculate the accurate answer.
When the formula is complex and cannot be transformed manually, use "Single Variable Solver" to set the target value and variable cells and let the software automatically calculate it.
The single-variable solution usually gives an approximate value. The result must be substituted back into the original formula to confirm that it is close enough to the target value.
The variable cell must be the input value used by the formula, not the result of the formula itself, otherwise it cannot be solved.
The number obtained by inference depends on whether it is reasonable. For example, the sales volume cannot be negative, and the quantity should usually be an integer.

When making forms, what we often encounter is that the results have already been determined, such as the target profit is set to a certain amount, the monthly payment does not exceed a certain upper limit, and the assessment score must reach 90 points, but we don’t know how much of the previous input should be filled in. At this time, forward calculation does not work, and you need to push back from the results. There are two common methods for backward calculation of Excel formulas: one is to deform the original formula and directly write out the calculation method of the input value; the other is to use "single variable solution" to let the software continuously adjust the input value until the formula result is close to the target.

This article is written for readers who often do budgets, quotations, financial calculations, and performance statistics. After reading, you will be able to judge which method is suitable for your problem, know how to set it up step by step, and know how to check whether the result is reliable after you get it. The following examples can be followed in your own table, no additional plug-ins are required.

How to perform backward calculation in Excel? One sentence conclusion

If the formula has a simple structure and can be manually moved, give priority to writing the inverse formula. The result will be accurate and easy to reuse in the future; if there are multiple layers of functions nested in the formula, or it cannot be deformed analytically, use "single variable solution". In both methods, you must first put the formula and target values into the table before starting.

your situation	Recommended practices	reason
The formula is simple addition, subtraction, multiplication and division, and terms can be moved	Write the inverse formula	The results are accurate and the table is automatically updated after changes are made.
The formula contains functions such as IF and VLOOKUP, which are difficult to transform manually.	Single variable solution	No need for derivation, just let the software calculate it directly
Only a one-time estimate is required, no long-term templates are required	Single variable solution	Set up quickly and get the answer in just a few steps
To repeatedly adjust the target value and review the results	reverse formula	Change the target value cell and the input value will change immediately
The input value must be an integer, such as the number of people or the number of pieces	Inverse formula addition and rounding, or manual rounding after single variable solution	The solution results often contain decimals and need to be processed according to actual rules.

Before you start: Confirm these points first

Sorting out a few questions before taking action can save you a lot of detours.

Whether the formula result is determined by known inputs. The premise of backcasting is that the target result can be calculated from one or several inputs. If the result also relies on random numbers, external data, or manual entry, the number obtained by backward calculation will change accordingly, and there is no fixed answer.
Which value is unknown. There can be only one unknown input. If there are two unknown quantities at the same time, single-variable solution cannot determine the unique result, and you need to add a condition or use another method.
Where the target value is written. It is recommended to occupy a separate cell, such as writing the target profit in B6, and reference it later. In this way, there is no need to change the formula when the target value changes, and it is easy to check.
Unknown whether the value of the cell is purely numeric. If the variable cell is text, formula or blank, the solution will be wrong. The cell content should be a numerical value that can be modified.
Is there any business restriction on the backward push results? For example, the unit price cannot be negative, sales volume is usually an integer, the proportion cannot exceed the whole, and discounts cannot make the selling price lower than the cost. These restrictions determine whether you need to adjust after you get the results.

Example of a reverse formula: Use sales volume to back-calculate the sales volume required to maintain capital or target profit.

The following uses a common example to illustrate how to write the inverse formula. Suppose you sell a product. The unit price, unit cost, fixed cost and target profit are all known. You need to calculate how many units you need to sell.

The forward formula is: profit = sales volume × (unit price − unit cost) − fixed cost. Transform it and require the profit to be equal to the target profit, and you get:

Sales volume = (fixed cost + target profit) ÷ (unit price − unit cost)

In the table, you can put the unit price in B1, the unit cost in B2, the fixed cost in B3, the target profit in B4, and then write in B5:

=(B3+B4)/(B1-B2)

The advantage of this formula is that the results are accurate. If the unit price or cost is changed, B5 will be updated immediately. Note that the unit price must be greater than the unit cost, otherwise the denominator is zero or negative and the result is meaningless. If your original formula has another structure, follow the same idea and gradually reverse the operations on both sides of the equal sign: addition becomes subtraction, multiplication becomes division, division becomes multiplication, and square turns into square root.

Steps to solve using single variable in Excel

Single-variable solving is more appropriate when the formula is difficult to transform manually. Its principle is: fix the value to be reached by the target cell, and continuously adjust the specified input cell until the formula result is equal to the target value. The following takes the "Profit Formula Counter Sales Volume" as an example and operates in sequence:

Create the forward formula in the table. Assume that the sales volume is written in B5 and the profit formula is written in B7, for example =B5*(B1-B2)-B3. First make sure that the formula can correctly calculate the profit, otherwise the solution will be meaningless.
Confirm the target value cell. The target value can be directly filled in B8, and fill in the number you want the profit to reach, which is convenient for later reference and modification.
Open the solver tool. Find the Solver function in Excel that brings the result of a formula to a specified number, known as Solve in One Variable. The entrance location may be different in different versions, whichever is actually found in the software.
Set the target cell. Select B7, the profit cell, this is where you want the result to reach a certain value.
Enter the target value. Fill in the target value input box with the number you want the profit to reach, which is the value written previously in B8.
Select variable cells. Select B5, the cell where the sales volume is located. It is the unknown quantity that needs to be found this time, and the software will adjust it to meet the goal.
Run the solver and view the results. After confirmation, the software will give an approximate solution and write it into B5. If the software prompts that a solution has been found, you can accept the result; if it prompts that it has not converged, you need to check the formula and initial values.
Put the result back into the formula. Check if B7 is close to the target value. After confirming that it is correct, proceed to the next step of inspection.

It should be noted here that the single-variable solution gives numerical approximation results. If the sales volume needs to be rounded, you can round the whole number first, then see if the profit is still close to the target, and compare between adjacent integers if necessary.

Check each item after completion

After getting the back-reduction results, do not use them directly. Check the following items one by one to avoid bringing wrong numbers into the report.

Check items	How to see	What to do if there is a problem
Substitute the result of the formula	Substitute the deduced input value back into the original formula and see the difference between the result and the target value.	When the gap is too large, check whether you have selected the wrong variable cell, or increase the solution accuracy and rerun the operation.
Are the results within a reasonable range?	Check with business common sense to see if the number is negative or exceeds the upper limit.	If it exceeds the range, it means that the target value may not be achieved, and the goals or conditions need to be adjusted.
Is an integer required?	Check whether the unit of the input value, such as the number of pieces and the number of people, must be an integer.	Round up or down according to actual rules and re-substitute into the formula to check the result.
Have other cells been modified by mistake?	Check whether the expected value is written in the variable cell and whether other formulas are normal	If the table has a reference relationship, restore the original value and solve it again to avoid chain errors.
Can the results be reproduced?	Modify the target value once and solve it again to see if the result meets the expected change.	If the results are unstable multiple times, check whether the formula contains random functions or circular references.

If the form is to be given to colleagues or clients, you can also keep the formulas and results in the worksheet, and indicate the target value and solution method next to it for review by others. If the table needs to be translated into other languages for use by overseas teams, OmniDoc supports translation of .

📊 Core comparison

There are three main methods of backward calculation, each with its own applicable scenarios:

method	advantage	limitations	Applicable scenarios
reverse formula	The results are accurate, reusable, and changes are updated immediately	You need to derive it yourself, and it is easy to make mistakes when the formula is complex.	Clearly structured formulas require long-term use of templates
Single variable solution	Get started quickly, no deduction required	Results are approximate and can only be adjusted for an unknown quantity	The formula is complicated or only one estimate is made
Manual trial calculation	No functions are required, and you can intuitively see the process of the results approaching the target.	Low efficiency, it is difficult to find the exact value when there are many numbers	Understand principles or make rough estimates

In actual work, a common practice is to first use single-variable solution to quickly obtain results. After confirming that the idea is correct, the results are then transcribed into a reverse formula to form a template that can be used for a long time.

🎯 Which method is suitable for you?

People who make budgets or financial models: The target value changes frequently. It is recommended to write a reverse formula and change the target cell to see the changes in the input value.
People who only need a one-time answer: For example, if you want to temporarily calculate a quotation, you can use a single variable to solve it and complete it in a few minutes.
There are many people who judge the conditions in the formula: For example, the commission is calculated based on different gears. Manual derivation is unrealistic, and single-variable solution is more labor-saving.
People who need to explain the process to others: You can write the forward formula first, and then use words to explain how to infer from the target value. The reverse formula is easier for readers to understand.

❓ Frequently Asked Questions (FAQ)

1. Why is there a slight gap between the single variable solution result and the target value?

The single-variable solution adopts a step-by-step approach, and the result is usually an approximate solution with a small error. As long as the difference is within an acceptable range, it can be used; if it needs to be accurate to an integer or specific decimal places, it can be manually rounded and returned to the formula for verification.

2. Single variable solution prompts that no solution that satisfies the conditions can be found. What should I do?

First check whether the variable cell really affects the target cell. If there is no relationship between the two, no matter how you adjust it, you will not get the result. Secondly, see whether the target value exceeds the possible value range of the formula. For example, the profit upper limit may be limited by fixed costs. You can also change the initial value, for example, fill in the variable cell with a number close to the answer, and then run the solution.

3. Can we infer two unknown quantities at the same time?

Single-variable solution can only adjust one cell. When there are two unknown variables at the same time, the answer is usually not unique. The solution is to add a condition, such as stipulating a fixed unit price, or stipulating a certain relationship between sales volume and cost, so that there is only one unknown quantity left.

4. Do we need to keep decimals in the results obtained by backward calculation?

It depends on the actual meaning. The amount can be kept to two decimal places, and the number of pieces and number of people should usually be integers. If the result is a decimal but actually must be an integer, it needs to be rounded according to business rules and re-substituted into the formula to confirm whether the goal can still be achieved.

5. Can machine translation or online tools replace manual verification?

cannot. Translation or conversion tools can help with text and table formatting, but numbers, formula references, and calculations still require manual verification. For important figures, it is recommended to compare them one by one with the original formula before use.

📝 Summary

The key to reverse formula calculation in Excel is to first determine whether the formula can be deformed manually: if it can be deformed, write the reverse formula, which is accurate and reusable; if it cannot, use a single variable to solve, set the target value and variable cells and let the software try the calculation. No matter which method is used, the results must be substituted back into the original formula to check and check whether they meet the actual limits.

Open OmniDoc and work with your spreadsheet file