How to use the function so that depending on the magnitude of the value in the cell A1 in the cell B1, the value is output provided that "if a1 <1000, then b1 = 100, if 1001

  • Maybe you need a ВПР function? It selects from the table sorted by key (from and to the next record) the desired value. - nick_n_a

2 answers 2

Depending on the task and the number of ranges, you can use different functions.

For one or three investments, the IF function is sufficient:

  =ЕСЛИ(A1<1000;100;101) =ЕСЛИ(A1>1000;1;ЕСЛИ(A1>700;2;3))

The first formula can be written in this form:

 =(A1>1000)+100

In Excel 2007 and later, embedding of functions up to 64 is allowed, but under a large number of conditions such a formula will be cumbersome and poorly readable. In this case, it is more convenient to have a lookup table (correspondence table).

In the first column of such a table we write the lower boundary values ​​of the ranges, in the second - the result for such ranges. Having a table, you can use the function of the CDF with the search for inaccurate matching:

 =ВПР(что_ищем;таблица_соответствий;2;1) =ВПР(что_ищем;таблица_соответствий;2)

Both formula entries are valid.

Required:

a) in the first row of the correspondence table must be entered 0, otherwise the values ​​that are less than the smaller one in the first column will cause an error of the formula # N / A;

b) the values ​​in the first column should be in ascending order.

Here is an example. Drawing from another topic, but the principle of operation displays:

enter image description here

If there are not very many ranges, you can write them directly into functions with an array of constants. For the example shown in the figure:

 =ВПР(D2;{0;0,1:20;0,15:50;0,2:104;0,25};2)

    The design of the conditions is as follows:

     =ЕСЛИ(условие;что делать если правда;что делать если ложь)

    Thus, your condition will look like this:

     =ЕСЛИ(A1<1000;100;ЕСЛИ(B1=100;50;ЕСЛИ(С1>20;23;24)));
    • you need something like this: = IF (F21 <1000; 100; 100); IF (1001 <F21 <5000; 200; 200); IF (5001 <F21 <15000; 400; 400) and so on, but the campaign is confusing with brackets - Serge
    • 1001 <F21 <5000 is correct as: AND (F21> 1001; F21 <= 5000). Sign = apply where it is necessary that the value falls within the range. With the use of the IF cascade, it is usually not necessary to test two conditions in the AND function, since one of the conditions is checked by the previous or next IF function. - vikttur