A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Hi,
You could try this alternative
=INDEX($A$3:$L$3,1,MATCH(TRUE,INDEX($A$4:$L$4>0,,),0)
A3:L3 has the financial years and A4:L4 has the numbers.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I need to be able to reliably calculate the first nonzero value in an array, then pull header info related to it. I've figured out a way to do this, but it doesn't work all the time (7 of 33 lines return the array end location even though there are nonzero values earlier in the array). Help?
To find the first nonzero value, I'm using MATCH(0,{cells},1). No, my values aren't in ascending order but they do all start with zeroes, have a group of nonzero values, then maybe return to zeroes at the end -- the lines that work return the location of the last (left-to-right on the row) zero value before nonzero values begin. I'm then using INDEX to pull the header I need from one column to the right of the returned MATCH location. When it works, it's great -- when it doesn't work, it returns the final position in the array, which causes a #REF! error after I add one to the column...
I'm just confused why it works some times, and not others. The cases where it doesn't work seem to be the smaller nonzero values, but increasing all values by 1000 didn't change the outcome. The other possible thought is shorter nonzero value strings (one or two values, rather than three or four).
Here's a working line: INDEX({"","","FY11","FY12","FY13","FY14","FY15","FY16","FY17","FY18","FY19","FY20"},1,MATCH(0,{0,0,0,0.35,0.45,0.45,0.45,0.45,0.45,0,0,0},1)+1)
Here's a nonworking line: INDEX({"","","FY11","FY12","FY13","FY14","FY15","FY16","FY17","FY18","FY19","FY20"},1,MATCH(0,{0,0,0,0.025,0,0,0,0,0,0,0,0},1)+1)
Anybody have a better approach? Thanks in advance!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Hi,
You could try this alternative
=INDEX($A$3:$L$3,1,MATCH(TRUE,INDEX($A$4:$L$4>0,,),0)
A3:L3 has the financial years and A4:L4 has the numbers.
Answer accepted by question author
As an array formula (confirm with Ctrl+Shift+Enter):
= INDEX({"","","FY11","FY12","FY13","FY14","FY15","FY16","FY17","FY18","FY19","FY20"},1,MATCH(TRUE,{0,0,0,0.025,0,0,0,0,0,0,0,0}>0,0))