Formula doesn't work

[rufuxinix]

Can anyone solve this problem? I can't get the following formuls to work. The formula seems to be correct but my Excel doesn't do what it's supposed to do.
The requirement is to extract the value of the cell above those which end in 5. I have enabled RC, Column A is Number, not sure what else to do.

 

[Joe4]

Which row is that first value/formula in?
If it is in row 1, you cannot use "R[-1]", as there are no rows before row 1!

You would either need to move this down at least one row so you start in row 2, or have a different formula to use for row 1.

 

[Fluff]

my Excel doesn't do what it's supposed to do.

What does it do? Showing a formula that doesn't work with no explanation about how it doesnt work, is not much help.
Also what row is that formula on?

 

[DanteAmor]

I have enabled RC

You mean you activated R1C1 Notation:

Why do you need to activate it?

If you decide to use A1 notation, it works:

varios 03dic2024.xlsm
	A	B
1	1
2	15	1
3	6	Nothing
4	15	6
5	7	Nothing
6	14	Nothing
7	15	14
Hoja1

Cell Formulas
Range	Formula
B2:B7	B2	=IF(RIGHT(A2,1)="5",A1,"Nothing")

Either way works, but you should start at least on row 2, as I put it in my example.

 

[Joe4]

You mean you activated R1C1 Notation:

View attachment 119978​

Why do you need to activate it?

If you decide to use A1 notation, it works:

varios 03dic2024.xlsm
	A	B
1	1
2	15	1
3	6	Nothing
4	15	6
5	7	Nothing
6	14	Nothing
7	15	14
Hoja1

Cell Formulas
Range	Formula
B2:B7	B2	=IF(RIGHT(A2,1)="5",A1,"Nothing")

It does not work if the first value and formula are in row 1.
Perhaps that is why they changed it to R1C1 reference style, because they could not use A0!
But doing that does not get around the problem that you cannot look at the previous row if you are in row 1 (that would be a formula logic issue, not a notation one).
When writing a formula that reference the previous row, the first line can present some challenges, and often needs to be handled differently.

 

[Cubist]

For more context, it's a continuation of this thread.

Finding the value of a cell above a cell that meets a condition in a column

In an Excel column I need to find the value of a cell that is immediately above. Example: If I have cell values that end in 5, I want to extract the value of the cell above them.

www.mrexcel.com

 

[Joe4]

Rufinix,

Please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.