| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help Please
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 0s. The 0s will be followed by a series of whole numbers (non-0 numbers). The whole numbers will then be followed by 0s. Again, remember that the whole numbers may be preceded by 0s 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 0s. 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 0s. 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help Please
Hello Bill, Assuming you have less than 1000 rows of data (adjust as necessary) try this formula in D1 =INDEX(C2:C1001,MATCH(1,INDEX((A1:A1000<0)*(A2:A1 001=0),0),0)) -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127731 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help Please
... 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 --- "Formula help" wrote: 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 0s. The 0s will be followed by a series of whole numbers (non-0 numbers). The whole numbers will then be followed by 0s. Again, remember that the whole numbers may be preceded by 0s 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 0s. 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 0s. 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help Please
Max:
This works for the first time a 0 appears but not after the series of whole numbers. Bill "Max" wrote: ... 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 --- "Formula help" wrote: 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 0s. The 0s will be followed by a series of whole numbers (non-0 numbers). The whole numbers will then be followed by 0s. Again, remember that the whole numbers may be preceded by 0s 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 0s. 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 0s. 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help Please
Did you try my suggestion Bill? -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127731 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help Please
Barry:
I believe your suggestion worked. Thank you very much. Bill "barry houdini" wrote: Did you try my suggestion Bill? -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127731 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
