Get value of steps

Question

Get value of steps

Asked 8 years, 11 months ago

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.

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?

– Luiz Vieira

2015/12/23 at 12:50
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.

– Luiz Vieira

2015/12/23 at 12:52
@John wanted to help you, improve the question! Thank you.

– David

2015/12/23 at 13:49
@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.

– bfavaretto

2015/12/23 at 15:05
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.

– Alexandre Cartaxo

2015/12/23 at 16:06
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.

– Luiz Vieira

2015/12/23 at 16:35
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.

– Luiz Vieira

2015/12/23 at 16:38
@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? :)

– Luiz Vieira

2015/12/23 at 16:40
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.

– Alexandre Cartaxo

2015/12/23 at 16:43
@Alexandrecartaxo I believe we have a distinct view of what is a constructive way. But, ok, no problems.

– Luiz Vieira

2015/12/23 at 16:45
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.

– Alexandre Cartaxo

2015/12/23 at 16:49
@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.

– Luiz Vieira

2015/12/23 at 17:10
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

2015/12/23 at 20:49
@bfavaretto Ok. I also think it should not be reopened yet until it manifests. :)

– Luiz Vieira

2015/12/23 at 21:55
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

– João Filipe Rocha

2015/12/24 at 18:17
1

Okay, I reopened the question (CC @Luizvieira)

– bfavaretto

2015/12/24 at 20:31
@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 ;)

– Guilherme Nascimento

2015/12/24 at 20:44

Show 12 more comments

1 answer

Browser other questions tagged excel

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

by Luiz Vieira • **34,160** points · Answer 1 · 2015-12-24T22:13:09+00:00

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:

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):