what formula do I make for ranges of values in excel?

Question

I need a formula in excel that identifies ranges, e.g.

0-30  = 20%
31-60 = 98,5%
61-90 = 100%
91-120 = 101,5%

if f2<= 30 inform 20%; if f2>30 and <60 inform 98,5%, etc.

How do I do?

Answer 1

My answer is a little late, but I hope it will be useful. Could replace the function SE for PROCV, as follows:

=PROCV(c1;A1:B4;2;1);

The table must be mounted as follows:

Note: as you have reported integer ranges I am only considering that there will be no decimal numbers (e.g. 30.2 - in this case it would not work).

Answer 2

If you have Excel 2016, you can use the function IFS

=IFS(C1<=30,0.2,AND(C1>30,C1<=60),0.985,AND(C1>60,C1<=90),1,AND(C1>90,C1<=120),1.015,TRUE,"")

or

=IFS(C1<=30,0.2,E(C1>30,C1<=60),0.985,E(C1>60,C1<=90),1,E(C1>90,C1<=120),1.015,Verdadeiro,""

Answer 3

I just made one here, I do not know if it fits what you intend to do, but the formula works in the same reasoning as what was requested.

Formula:

=SE(F2<30;"20%";SE((F2>=30)*OU(F2<61);"98,5%";SE((F2>=61)*OU(F2<91);"100%";"101,5%")))

I hope I’ve helped!

Answer 4

Use the "SE" function (or "IF" if your office is in English).
This function has the following format:
SE( "condition"; A; B)
where A is the answer if the condition is true and B is the answer if the condition is false.

Example:
SE(F2<=30; 20%; 100%)
In this case if the content of cell F2 is less than or equal to 30, the formula will return the value 20%, otherwise it will return 100%.

To solve your problem, we must use several times the same formula, one inside the other, as below:

SE(F2<=30; 20%; SE(F2<=60; 98,5%; SE(F2<=90; 100%; 101,5%)))

Note that case F2<=30 (less than or equal to 30) returns 20%, so the next test does not need to verify if F2 is greater than thirty and lower OU equal to 60, because it is already being performed only when F2 is greater than 30.

If the result appears as 0.2 instead of 20%, just use cell formatting as a percentage ( % icon of format bar).

I hope the answer is clear.
Hug.

Answer 5

[

Using the SE function with the E condition, you can name the ranges.

Follows the formula;

=SE(E(B3>=0;B3<=30);$H$3;SE(E(B3>=31;B3<=60);$H$4;SE(E(B3>=61;B3<=90);$H$5;SE(E(B3>=91;B3<=120);$H$6))))

I hope I’ve helped!