Formula Help Please

F

Formula help

A B C D
0 7 0 9
0 7 6
7 8 5
6 2 0
4 7 6
3 5 5
1 0 7
0 8 9
0 4 8
0 7 2

I have three columns of numbers. Column A will have a series of numbers that
may or may not start with 0’s. The 0’s will be followed by a series of whole
numbers (non-0 numbers). The whole numbers will then be followed by 0’s.
Again, remember that the whole numbers may be preceded by 0’s or may not. In
other words, cell A1, A2, etc. may have a 0 or may have a whole number. The
whole numbers will always be followed by 0’s.

Columns B and C will have a random series of numbers.

Each time a run a program (import data), the numbers in Column A will
change. The new series of numbers may or may not begin with a 0 but will
always end with a 0 or number of 0’s. The numbers in Column B will also
change but the numbers in Column C will not change.

I would like to show in cell D1, the number in Column C that corresponds to
the first time a 0 will appear in column A following the series of whole
numbers. In the example above, 9 is the number in column C that corresponds
to the first time a 0 appears in column A following the series of whole
numbers.

I need the formula for cell D1.

Thanks for your help.

Best Regards,

Bill
 
B

barry houdini

Hello Bill,

Assuming you have less than 1000 rows of data (adjust as necessary) tr
this formula in D1

=INDEX(C2:C1001,MATCH(1,INDEX((A1:A1000<>0)*(A2:A1001=0),0),0)
 
M

Max

... show in cell D1, the number in Column C that corresponds to
the first time a 0 will appear in column A following the series of whole
numbers

Try in D1, array-entered (confirm by pressing CTRL+SHIFT+ENTER):
=INDEX(C1:C10,MAX(IF(A1:A10<>0,ROW(A1:A10)))+1)

voila? hit YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
 
F

Formula help

Max:

This works for the first time a 0 appears but not after the series of whole
numbers.

Bill
 
D

Don Guillett

Did you use ctrl+shift+enter. Show us a series of numbers where it doesn't
work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top