1

Topic: Dependence of value in one cell from value in other cell - how to construct?

Hello! I ask the help.
I made in  the table in which on the basis of input parameters (are selected in a picture) and experimental the data (the long table from below) is calculated certain result (cell H5). My task - to find values parameters at which the result is minimum. For this purpose I plan to use the unit of search of the decision built in an ex-fur-tree.
Problem in that operation of this unit extremely strongly depends on starting values parameters . Thus, now my task - to select good starting values parameters .
For this purpose I want to set any adequate (but not too successful) values all parameters , and then to vary each of them individually and to look, how changes result .
The Question : how to me, without spoiling the initial table, to construct (for example, on new sheet) dependence result from each of parameters (at fixed values of the remaining parameters specified in the initial table)? For example, I know that all y0 lie in a range from 0 to 0,00001, and all w lie in a range from 0 to 10. I can break these ranges, for example, into 100 points, to fill with values the first column of new sheet... And here is how to fill the second column?
It is obvious that the task a straight line and has the decision. But I simply do not know, on what keywords for it to search. Help, please!
I search most for the common decision because I expect that in the future the amount of parameters still increases.

2

Re: Dependence of value in one cell from value in other cell - how to construct?

For some reason the picture is not attached. Here the link to it.
https://preview.ibb.co/eAg3Ve/input.jpg

3

Re: Dependence of value in one cell from value in other cell - how to construct?

EXEL can solve the task of selection of ONE parameter, but not selection of several parameters.
Can probably solve the task purely mathematical way?
Lay out not a picture, and itself Excel a file.
Write down mandatory ranges of parameters.
In a forehead it is possible to construct a five-measured matrix with a certain step and to look where are a minimum.
If it is some minima, the task is not absolutely trivial.

4

Re: Dependence of value in one cell from value in other cell - how to construct?

982183 wrote:

EXEL can solve the task of selection of ONE parameter, but not selection of several parameters.

It with what such ? How many you will tell, so much and selects...

Sergey T. wrote:

the Problem that operation of this unit extremely strongly depends on starting values parameters . Thus, now my task - to select good starting values parameters .

it is better to attend at first to a choice of a method of approach - there where one passes, another at the same initial easily reaches the sufficient decision.

982183 wrote:

lay out not a picture, and itself Excel a file.

+100500
On photos we do not treat...

5

Re: Dependence of value in one cell from value in other cell - how to construct?

File: https://ru.files.fm/u/ttpwyfpd

wrote:

EXEL can solve the task of selection of ONE parameter, but not selection of several parameters

I Will disagree smile There is official superstructure Solver which is able to solve non-linear tasks with set of variables. Itself today learned and checked up. Here :
[youtube=DOCzyB8zj-8]

wrote:

In a forehead it is possible to construct a five-measured matrix with a certain step and to look where are a minimum.

There a problem - at me not five, but 15 parameters - (three Fojgt of a maximum in a spectrum). Such matrix should be built improbably long, and then also to understand with step, to find a minimum.
Yes, I know that the task in view can be solved,  each maximum separately. But it is the test data, then there will be the same, but maxima will be superimposed against each other.

wrote:

if it is some minima, the task is not absolutely trivial

Yes, therefore I and want to find "successful" input parameters manually.
Actually I want to construct "cutoffs" of this most  matrixes that on them to find "wrong" minima and to avoid them.
And consequently I ask a question about operation with  - how to receive dependence of "result" on one parameter with fixing of the remaining? Probably, it is possible such to write something in a BASIC-superstructure?

6

Re: Dependence of value in one cell from value in other cell - how to construct?

Akina wrote:

it is passed...
It with what such ? How many you will tell, so much and selects...

I about "Analysis" that if "" - Goal seek. (To install in cell  Value Y Changing cell value Z)
And you about what?

7

Re: Dependence of value in one cell from value in other cell - how to construct?

Sergey T. wrote:

There is official superstructure Solver which is able to solve non-linear tasks with set of variables. Itself today learned and checked up.

Did not know. Thanks. I will check up.

8

Re: Dependence of value in one cell from value in other cell - how to construct?

But I will repeat - I ask not about minimum search, namely how to collect all my long calculation in the table in one "function" which can be used in other sheets.

9

Re: Dependence of value in one cell from value in other cell - how to construct?

Sergey T. wrote:

how to collect all my long calculation in the table in one "function" which can be used in other sheets.

At first transform this "long calculation" into one mathematical formula. And then think that is easier - to draw a many-storeyed disgrace sheet formulas, or to write VBA-function.

10

Re: Dependence of value in one cell from value in other cell - how to construct?

It was recalled:

 I do not ask how it is necessary to put on trousers.
I ask about why they through a head do not fit. 

Without insults.

11

Re: Dependence of value in one cell from value in other cell - how to construct?

Sergey T. wrote:

the Question : how to me, without spoiling the initial table, to construct (for example, on new sheet) dependence result from each of parameters (at fixed values of the remaining parameters specified in the initial table)? For example, I know that all y0 lie in a range from 0 to 0,00001, and all w lie in a range from 0 to 10. I can break these ranges, for example, into 100 points, to fill with values the first column of new sheet... And here is how to fill the second column?

Well if you break a range of each parameter into 100 segments and count the table for each variant, you make operation for decision Search, only much more slowly smile
it is enough to break a range of each parameter into 10 segments - at 5 parameters it already 100000 variants.
How to make - a macro with nested loops to form the next variant, to inscribe it in pay sheet parameters, and in the next line of other sheet to inscribe these parameters and value of target cell 5.

12

Re: Dependence of value in one cell from value in other cell - how to construct?

Not all functions are shorthanded by this method.
Even on a plane.
http://www.yotx.ru/#!1/3_h/ubZ/tn@35Sam … 7s7m/tAwY=

13

Re: Dependence of value in one cell from value in other cell - how to construct?

wrote:

How to make - a macro with nested loops to form the next variant, to inscribe it in pay sheet parameters, and in the next line of other sheet to inscribe these parameters and value of target cell 5.

Thanks for the normal answer, I so made. But thought that there is any standard tool for this purpose.

wrote:

Not all functions are shorthanded by this method.

it is unconditional. A question in that, how much well you imagine function as a whole, what boundary conditions for variables, well and, the most important thing, in selection of starting values. There are also other algorithms, but any of them, I so understand, does not make magic. All the same it is necessary to use them correctly.

14

Re: Dependence of value in one cell from value in other cell - how to construct?

And it is possible to present something in six-measured space?