Excel - Finding the First Positive Value
Solved
Anonymous user
-
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention -
Hello,
I have a table in Excel 2007 in the cells A12:B194.
In another cell, I would like to obtain the value from column A of this table corresponding to the first positive value found in column B of the table.
I tried the following formula: =SEARCH(0;B12:B194;A12:A194)
but it gives me a result of a negative value close to 0.
It's not far because it's the value from the row just above that I should have! But it's not the right one.
Thank you for your help.
--
As the great philosopher Mick Jagger said: "You can't always get what you want."
Help me to help you!
I have a table in Excel 2007 in the cells A12:B194.
In another cell, I would like to obtain the value from column A of this table corresponding to the first positive value found in column B of the table.
I tried the following formula: =SEARCH(0;B12:B194;A12:A194)
but it gives me a result of a negative value close to 0.
It's not far because it's the value from the row just above that I should have! But it's not the right one.
Thank you for your help.
--
As the great philosopher Mick Jagger said: "You can't always get what you want."
Help me to help you!
2 answers
-
Hello
1°) You do not specify whether in your table you want to find the 1st value in the order of the table or in the order of magnitude?
2°) Is your table sorted, and if so, in what order?
If it is, as I understood, to find the value of A corresponding to the first positive number in B in the order of the table, try:
=INDEX(A12:A194;MATCH(0;B12:B194)+1)
for the first positive value BELOW a negative value
(-1 for the inverse)
Kind regards
Let’s ask ourselves if we are not alone in understanding what we are explaining? -
Hi Vaucluse,
always up early, I see! ^^
You understood well and the formula you provided works perfectly.
I thought about using a "+1", but I thought it sounded more like "patching up" than "academic".
But since it works, I'll stick with it.
Thanks and have a good Sunday ;-)
--
As the great philosopher Mick Jagger said: "You can't always get what you want"
Help me to help you!
