Hi Guys!
I have a column where I need to find the first non zero value and have whatever that lowest value is be returned in a different cell. In the example provided it would be 5 that Id liked returned in A16. The colunm itself changes based upon other inputs so any one cell from A1 till A13 could be all 0. Ive been using =INDEX(A1:A14,MATCH(0,A1:A14,-1)) in array format as well but it just keep returning 12 as the answer.
Any thoughts?
Much love,
Chin
You could use this Array Formula.
=INDEX(A1:A1000,MATCH(TRUE,A1:A1000<>0,0))
It must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glenn,
Thank you your formula works but only if the 0 is on the right hand side of the column not the left. Ive attached a screenshot with the formula I used to calculate those 0s. As you can see at the very bottom of the screen shot my output is still 0 and not 5200 as I'd like it to be. Any ideas?
Late to the party here, but your formula should beOriginally Posted by chinchillin
=IF($C$2:$C$51<$B$5,0,(ugly thing in parens))
with the zero NOT in quotes. This returns zero as a numerical value, not a text label.
Try this array formula
=INDEX(A:A,SMALL(IF(--A1:A100<>0,ROW(A1:A100)),1))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I'm heading out now and I can't read your screenshot on my phone's screen. But, if the zero is in the left, it is because it is text that looks like a zero. In an empty column you could do something like:
=--A1
and copy down. Alter the array to look in the new column. The -- coerces Excel into recognising a text 0 as a numerical 0.
Another way.....array-entered alsoFormula:
=INDEX($A$1:$A$14,MATCH(1,($A$1:$A$14<>0)*ISNUMBER($A$1:$A$14),0))
Thanks a lot for all of the help everyone you solved the problems no prob,much appreciated!!!
Likewise here, too....and thanks for the rep!
Thank you for the feedback!
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).
Likewise - thanks for the Reputation. Much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks