Get value of steps

Asked

Viewed 427 times

2

I wanted to know what(s) a(s) formula(s) to get a value from a predefined table.

For example, consider that in a spreadsheet I have the following table: 

| A   |  B   |
|-----|------|
| 245 | 4,5% |
| 256 | 6%   |
| 300 | 7%   |

Thus, when typing in another cell any value, for example, 256, i would like a formula that automatically finds the value of the corresponding step in the table (in this case, 6%).

Explaining better: when placing a value between 0 and 245 4.5 was returned if it placed a value between 246 and 256 6% was returned and so on.

excel
  • 2

    @bfavaretto, shouldn’t this question have been closed as unclear? Formulas in Excel are within the scope of the site (because there are already many questions of this type here). No?

  • John, welcome to Sopt. Your question does not seem very clear to me. This "example" you post, is the content of one or more cells? Is it a text string? If so, is it difficult to search for a value (a sub-string) in it and find the corresponding percentage? Please edit the question to provide more details. This will help you get answers faster and will certainly help to keep your question from being closed/suspended and/or negative. And if you haven’t already, read [help], because this site is not a forum.

  • @John wanted to help you, improve the question! Thank you.

  • @Luizvieira For me it should be off, but ok... I reopen as soon as he clarifies what is not clear. João Filipe, we need to understand what is the criterion of your sequence of numbers in column B.

  • 1

    -1: His idea is as follows: up to 245 returns 4.5%; from 245 (exclusive) to 256 returns 6%, etc. Only AP works by telepathy.

  • 2

    @bfavaretto If you want we can open a discussion in Meta about questions about formulas being in scope or not. I have the opinion that they are because it does not fail to involve programming and even some levels of logic.

  • João, thank you for editing and trying to improve. I also edited the question to try to improve it a little more. But still there are doubts. In your original question you mention that it should be 7% for the value 257, but this value is not in your table. If you do not explain exactly what you need and what your difficulty is, it’s going to be really hard for anyone to help you.

  • @Alexandrecartaxo Ok, it’s your right to vote for -1. And the AP is really struggling to explain what you need. But, you don’t think it’s more useful for the community to help constructively? :)

  • 4

    @Luizvieira I already helped in a constructive way: I used my telepathic wave converter to translate the question. And I can help even more: look for the PROCV (or VLOOKUP) function. Also, as I understand it, the negative votes should be cast when the issues have problems. I have endeavoured, as I often do, to justify my negative vote so that the PA can correct the issue.

  • @Alexandrecartaxo I believe we have a distinct view of what is a constructive way. But, ok, no problems.

  • 1

    @Luizvieira What would you suggest then? Edit the question as I imagine the user wants to express himself? If someone does not even bother to be as clear as possible when asking for other people’s help, I find it a great lack of education and commitment. But this, I admit, may have to do with cultural differences. I don’t know if they tolerate things more in Brazil than I do. Moreover, the questioner is in Portugal, according to his profile.

  • @Alexandrecartaxo Sorry, it was not my intention to create an unrelated discussion here. So I’m not going to continue this interaction, okay? But since you asked, I would suggest not using irony on a newcomer. You are absolutely right about the errors in the question, only that I believe it would be more useful to help you to improve the content without taking it away from the site.

  • 1

    @Luizvieira No need to discuss the goal, since the closing was a unilateral decision of mine, and I can reverse it. The only reason I have not reopened is because the question remains confused. It would be important for the author of the question to speak here in the comments!

  • @bfavaretto Ok. I also think it should not be reopened yet until it manifests. :)

  • 1

    Thanks @Luizvieira for "straightening" the question... but the goal is to get the value of the percentage, explaining better, by putting a value between 0 and 245 was returned 4.5 if you put a value between 246 and 256 was returned 6% and so on

  • 1

    Okay, I reopened the question (CC @Luizvieira)

  • @bfavaretto actually I did not say it should be reopened, but rather closed as "Not clear", in the case previously was closed as "outside the scope", totally agree to close ;)

Show 12 more comments

1 answer

3


Like his colleague Alexandre had already advanced in a comment, there is a function available in Excel called PROCV (in the Portuguese version, or VLOOKUP in the English version) that allows automated searches in tables. The syntax for the use of this function is the following:

=PROCV(Valor a ser Buscado;Range da tabela para busca;Índice numérico da coluna a ser devolvida)

Note that if your Excel version is in English you should separate the parameters with comma (,) in place of semicolons (;).

To make your solution more automated, I suggest naming your table (for example, using the name Escalões), and then use that name instead of crease. To do this, just select the table and give a name in the field available on the left side of the formulas, next to the Ribbon:

inserir a descrição da imagem aqui

So you can use, for example, the following call to get the desired value:

=PROCV(D4;Escalões;2)

Note that the function stops at the last line with a value less than or equal to the searched one, so it works the way you want it to (using the same range range). Only you need to have at least one line with the default value for values less than 245 (if they exist), otherwise the formula returns error (as illustrated below):

inserir a descrição da imagem aqui

  • Our cool Luiz and @Alexandrecartaxo - But a doubt, why 200 caused #N/D, 200 is not in the "range" from 0 to 245?

  • I explained in the reply. The function stops at the last value line less than the sought. As it does not exist and no line previous, gives error. If you want, add a line with value 0 just to have this return.

  • 1

    A understood the range is from A:1 until A:2 - 1 (made me understand? or got confused rs), now made sense, very good +1

  • thank you very much for the answer. But if I put 246 will give 4,5% and the goal was to give 6

  • 1

    It is very simple to change to make it work: build your table so that the values are the initial of the intervals, and not the final ones. For example, instead of having 245 (the last of the previous interval) with 4,5%, define the 246 (the first of the next interval) with 6%.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.