Excel: How to get last result from above
up vote
0
down vote
favorite
I have a problem that is described in the picture below. Until today, I have done manual changes to the formula and that will take time if I have massive data.
Anyone can help? I want to fix B7 and the next cell if I found another gap.
This is what i use:
microsoft-excel
edited Nov 22 at 6:34
Dave
23.2k74362
asked Nov 22 at 4:22
Grideas
63
add a comment |
up vote
0
down vote
favorite
I have a problem that is described in the picture below. Until today, I have done manual changes to the formula and that will take time if I have massive data.
Anyone can help? I want to fix B7 and the next cell if I found another gap.
This is what i use:
microsoft-excel
edited Nov 22 at 6:34
Dave
23.2k74362
asked Nov 22 at 4:22
Grideas
63
Can you please edit your question to 1) add the formula you are currently using (as text) 2) let us know exactly what the expected output is
– cybernetic.nomad
Nov 22 at 5:09
Can you not remove that row?
– Dave
Nov 22 at 6:43
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a problem that is described in the picture below. Until today, I have done manual changes to the formula and that will take time if I have massive data.
Anyone can help? I want to fix B7 and the next cell if I found another gap.
This is what i use:
microsoft-excel
edited Nov 22 at 6:34
Dave
23.2k74362
asked Nov 22 at 4:22
Grideas
63
I have a problem that is described in the picture below. Until today, I have done manual changes to the formula and that will take time if I have massive data.
Anyone can help? I want to fix B7 and the next cell if I found another gap.
This is what i use:
microsoft-excel
microsoft-excel
edited Nov 22 at 6:34
Dave
23.2k74362
asked Nov 22 at 4:22
Grideas
63
edited Nov 22 at 6:34
Dave
23.2k74362
asked Nov 22 at 4:22
Grideas
63
edited Nov 22 at 6:34
Dave
23.2k74362
edited Nov 22 at 6:34
Dave
23.2k74362
edited Nov 22 at 6:34
Dave
23.2k74362
23.2k74362
asked Nov 22 at 4:22
Grideas
63
asked Nov 22 at 4:22
Grideas
63
asked Nov 22 at 4:22
Grideas
63
63
Can you please edit your question to 1) add the formula you are currently using (as text) 2) let us know exactly what the expected output is
– cybernetic.nomad
Nov 22 at 5:09
Can you not remove that row?
– Dave
Nov 22 at 6:43
add a comment |
Can you please edit your question to 1) add the formula you are currently using (as text) 2) let us know exactly what the expected output is
– cybernetic.nomad
Nov 22 at 5:09
Can you not remove that row?
– Dave
Nov 22 at 6:43
Can you please edit your question to 1) add the formula you are currently using (as text) 2) let us know exactly what the expected output is
– cybernetic.nomad
Nov 22 at 5:09
Can you please edit your question to 1) add the formula you are currently using (as text) 2) let us know exactly what the expected output is
– cybernetic.nomad
Nov 22 at 5:09
Can you not remove that row?
– Dave
Nov 22 at 6:43
Can you not remove that row?
– Dave
Nov 22 at 6:43
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
You can utilize the LOOKUP formula to find the next non-empty value from above. For example, in row 7, column B (like in your example), enter
=LOOKUP(2,1/(B$1:B6<>""),B$1:B6)
This will find the next non-empty value in the area above B7. I have placed the $ characters intentionally for copying the formula down the column.
(detailed explanation of the formular here)
If you have several thousands of rows, this may become a little bit slow, since the above formula leads to quadratic run time behaviour. If thats the case, but there is a known limit for the size of your gaps (for example, 2 rows), you can restrict the lookup to this specific number of rows, like
=LOOKUP(2,1/(B4:B6<>""),B4:B6)
This checks only the three rows above B7 for valid values.
answered Nov 22 at 6:57
Doc Brown
318312
sorry for slow respond, i will test it now
– Grideas
Nov 29 at 7:54
thank you, it work
– Grideas
Nov 29 at 8:09
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You can utilize the LOOKUP formula to find the next non-empty value from above. For example, in row 7, column B (like in your example), enter
=LOOKUP(2,1/(B$1:B6<>""),B$1:B6)
This will find the next non-empty value in the area above B7. I have placed the $ characters intentionally for copying the formula down the column.
(detailed explanation of the formular here)
If you have several thousands of rows, this may become a little bit slow, since the above formula leads to quadratic run time behaviour. If thats the case, but there is a known limit for the size of your gaps (for example, 2 rows), you can restrict the lookup to this specific number of rows, like
=LOOKUP(2,1/(B4:B6<>""),B4:B6)
This checks only the three rows above B7 for valid values.
answered Nov 22 at 6:57
Doc Brown
318312
sorry for slow respond, i will test it now
– Grideas
Nov 29 at 7:54
thank you, it work
– Grideas
Nov 29 at 8:09
add a comment |
up vote
1
down vote
accepted
You can utilize the LOOKUP formula to find the next non-empty value from above. For example, in row 7, column B (like in your example), enter
=LOOKUP(2,1/(B$1:B6<>""),B$1:B6)
This will find the next non-empty value in the area above B7. I have placed the $ characters intentionally for copying the formula down the column.
(detailed explanation of the formular here)
If you have several thousands of rows, this may become a little bit slow, since the above formula leads to quadratic run time behaviour. If thats the case, but there is a known limit for the size of your gaps (for example, 2 rows), you can restrict the lookup to this specific number of rows, like
=LOOKUP(2,1/(B4:B6<>""),B4:B6)
This checks only the three rows above B7 for valid values.
answered Nov 22 at 6:57
Doc Brown
318312
sorry for slow respond, i will test it now
– Grideas
Nov 29 at 7:54
thank you, it work
– Grideas
Nov 29 at 8:09
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You can utilize the LOOKUP formula to find the next non-empty value from above. For example, in row 7, column B (like in your example), enter
=LOOKUP(2,1/(B$1:B6<>""),B$1:B6)
This will find the next non-empty value in the area above B7. I have placed the $ characters intentionally for copying the formula down the column.
(detailed explanation of the formular here)
If you have several thousands of rows, this may become a little bit slow, since the above formula leads to quadratic run time behaviour. If thats the case, but there is a known limit for the size of your gaps (for example, 2 rows), you can restrict the lookup to this specific number of rows, like
=LOOKUP(2,1/(B4:B6<>""),B4:B6)
This checks only the three rows above B7 for valid values.
answered Nov 22 at 6:57
Doc Brown
318312
You can utilize the LOOKUP formula to find the next non-empty value from above. For example, in row 7, column B (like in your example), enter
=LOOKUP(2,1/(B$1:B6<>""),B$1:B6)
This will find the next non-empty value in the area above B7. I have placed the $ characters intentionally for copying the formula down the column.
(detailed explanation of the formular here)
If you have several thousands of rows, this may become a little bit slow, since the above formula leads to quadratic run time behaviour. If thats the case, but there is a known limit for the size of your gaps (for example, 2 rows), you can restrict the lookup to this specific number of rows, like
=LOOKUP(2,1/(B4:B6<>""),B4:B6)
This checks only the three rows above B7 for valid values.
answered Nov 22 at 6:57
Doc Brown
318312
edited Nov 22 at 22:47
answered Nov 22 at 6:57
Doc Brown
318312
answered Nov 22 at 6:57
Doc Brown
318312
answered Nov 22 at 6:57
Doc Brown
318312
318312
sorry for slow respond, i will test it now
– Grideas
Nov 29 at 7:54
thank you, it work
– Grideas
Nov 29 at 8:09
add a comment |
sorry for slow respond, i will test it now
– Grideas
Nov 29 at 7:54
thank you, it work
– Grideas
Nov 29 at 8:09
sorry for slow respond, i will test it now
– Grideas
Nov 29 at 7:54
sorry for slow respond, i will test it now
– Grideas
Nov 29 at 7:54
thank you, it work
– Grideas
Nov 29 at 8:09
thank you, it work
– Grideas
Nov 29 at 8:09
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1377467%2fexcel-how-to-get-last-result-from-above%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you please edit your question to 1) add the formula you are currently using (as text) 2) let us know exactly what the expected output is
– cybernetic.nomad
Nov 22 at 5:09
Can you not remove that row?
– Dave
Nov 22 at 6:43