x+(y*?)=Z when a value for both Z and ? is given

[RBGuy]

Is there an Excel formula that I can paste into a spreadsheet that will spit out every combination of answers for X and Y when a value is given for Z and the question mark in the example formula x+(y*?)=Z that I don't know how to set up to do what I need it to do in Excel?


I'm looking for a formula in an Excel sheet in which the value for Z and the value multiplied against Y will be entered into the formula so that the Excel spreadsheet only has to generate every combination for X & Y to make the formula correct. In other words, the formula's task in the spreadsheet is to determine every combination for X & Y in which X plus (Y multiplied by the value I chose in the formula) equals the value I chose in the formula for Z.




The value I will be using for Z will range from 4000 to 7000. The value for ? will be a decimal with up to nine digits to the right of the decimal like 1.014561667


I only need to see how this can work for x+(y*1.014561667)=6000 and can use that formula or spreadsheet for the other scenarios I want


If there is a more clumsy way of accomplishing this like filling the spreadsheet with 4000 to 7000 answers, I'm ok with it and can search for the exact or closest X & Y values that are generated for the Z value that I chose.


This seems simple and I know how to use Excel, but can't seem to figure this one out.

 

[joeu2004]

Is there an Excel formula that I can paste into a spreadsheet that will spit out every combination of answers for X and Y when a value is given for Z and the question mark in the example formula x+(y*?)=Z that I don't know how to set up to do what I need it to do in Excel?

"Every combination"?!

Not really, because there are an "infinite" number of pairs of x and y that can be used.

Suppose cell Z1 is you z-value (6000, say), and W1 is your ?-value (1.014561667, say).

For any x-value in X1, the y-value can be determined by the following formula in Y1:

=($Z$1-X1)/$W$1

Even if we limit X1 to integers that we can enter manually and Excel can represent exactly, there are nearly 2 trillion integers between -999,999,999,999,999 and +999,999,999,999,999.

We can enter larger integers. But Excel cannot represent integers greater in magnitude than 9,007,199,254,740,992 (2^53) exactly. And Excel will not display all of those integers correctly, because Excel arbitrarily formats only up to 15 significant digits.

And if X1 is not limited to integers, there are an "infinite" number of fractional values in between each of the 2 trillion integers.

Moreover, there are an "infinite" number of integers and non-integers less than -999,999,999,999,999 and greater than +999,999,999,999,999 that Excel can calculate.

The good news is: for any particular value of X1, there is only one value of Y1, which can be calculated by the formula above.

So if you limit X1 to a reasonably small set of values (100, say), just fill X1:X100 with those values and fill Y1:Y100 with the formula above, and you will determine 100 pairs of x and y.