First non-zero cell in a range of columnal cells

Previously I had asked how to get the value from a cell which lines up with the first nonzero cell in a range. Things are a LITTLE more complicated now.

Referencing the picture. I need to find the value in column c for the first F/G/H row that has a non-zero value (in this case row 138).

The answer I received before was selection as the solution. And it worked for a while until the client did something new--she didn't put a value in F/G/ AND H.

So I have to account for rows which have 1, 2, or 3 values in a row.

I'm only interested in which is the first row encountered with a value in F/G OR H.

(Must be formula. Some clients will not allow macros to run on their machines).

Excel 2010.
Nonzero.jpg
author3000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Column I1: =IF(SUM(F1:H1)>0,1,0) copy down.

Then =MATCH(1,I:I,0) will find the first 1, = row number.
0
barry houdiniCommented:
Assuming you have data in rows 130 to 140 (adjust as required), you can use this "array formula"

=IFERROR(INDEX(C130:C140,SMALL(IF(F130:H140>0,ROW(C130:C140)-ROW(C130)+1),1)),"")

confirmed with CTRL+SHIFT+ENTER

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
If the client is using Excel 2010 or later, you can take advantage of the AGGREGATE function:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(F130:H145)*(F130:H145/F130:H145),1)),"")

The above formula does not need to be array-entered.
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

author3000Author Commented:
Will the Aggregate function work on a 2010 Excel spreadsheet that is run on a computer running, oh, Windows XP, if the compatability packs have been installed?
0
byundtMechanical EngineerCommented:
It doesn't matter if you have compatibility packs--you need to be using Excel 2010 or later for the AGGREGATE function to work.
0
author3000Author Commented:
I have chosen these two answers because

(a) they require only one cell to have a formula.

This is important because when clients make requests for changes the fewer the number of cells there are to update the more efficient that process is. In addition, formulae need to be hidden and having formulae in column I would require I to be hidden. There is a VERY good chance it would get overlooked on an upgrade. It's a good solution but just just a good fit in this case.

(b) My clients variously use Excel 2003 and forward all the way to the latest. Having a solution that fits both pre-2010 and one that fits later helps us a lot. When we get into an older version spreadsheet the mindset shifts to just what works in the pre-2010 version. The same paradigm applies with the 2010 and 2010+ versions.

Thanks for the solutions. The spreadsheet development is moving forward again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.