Home |
Search |
Today's Posts |
#1
|
|||
|
|||
First Occurence of Non Blank Cell in row array
I have an array of yearly dates in A2:A20. I then have
an array below it B2:B20 that the user can enter costs into. I want to be able to identify the year that the first cost appears in. The cells that do not have numbers will be left blank. How do I do this? I know I can count the occurences of cells with data: Sumproduct(--(B2:B200)) But how do I identify which column or year the first of these occurs in? Thanks |
#2
|
|||
|
|||
figured it out with an array formula
{Index(A2:A20,Match(TRUE,NOT(ISBLANK(B2:B20)),0)} -----Original Message----- I have an array of yearly dates in A2:A20. I then have an array below it B2:B20 that the user can enter costs into. I want to be able to identify the year that the first cost appears in. The cells that do not have numbers will be left blank. How do I do this? I know I can count the occurences of cells with data: Sumproduct(--(B2:B200)) But how do I identify which column or year the first of these occurs in? Thanks . |
#3
|
|||
|
|||
=INDEX(A2:A20,MATCH(1,1/(LEN(B2:B20)0),0))
Array-entered. Press ctrl + shift + enter. HTH Jason Atlanta, GA -----Original Message----- I have an array of yearly dates in A2:A20. I then have an array below it B2:B20 that the user can enter costs into. I want to be able to identify the year that the first cost appears in. The cells that do not have numbers will be left blank. How do I do this? I know I can count the occurences of cells with data: Sumproduct(--(B2:B200)) But how do I identify which column or year the first of these occurs in? Thanks . |
#4
|
|||
|
|||
Just out of curiosity, my model crashes with I enter my
array formula. I am copying it down 200 rows. Does anyone know of a solution that does not involve an array formula? Thanks -----Original Message----- =INDEX(A2:A20,MATCH(1,1/(LEN(B2:B20)0),0)) Array-entered. Press ctrl + shift + enter. HTH Jason Atlanta, GA -----Original Message----- I have an array of yearly dates in A2:A20. I then have an array below it B2:B20 that the user can enter costs into. I want to be able to identify the year that the first cost appears in. The cells that do not have numbers will be left blank. How do I do this? I know I can count the occurences of cells with data: Sumproduct(--(B2:B200)) But how do I identify which column or year the first of these occurs in? Thanks . . |
#5
|
|||
|
|||
Model would crash if I copied array formula down to many
rows at once. Copies a few rows at a time and it seems to work now???? Not sure why but it works. -----Original Message----- Just out of curiosity, my model crashes with I enter my array formula. I am copying it down 200 rows. Does anyone know of a solution that does not involve an array formula? Thanks -----Original Message----- =INDEX(A2:A20,MATCH(1,1/(LEN(B2:B20)0),0)) Array-entered. Press ctrl + shift + enter. HTH Jason Atlanta, GA -----Original Message----- I have an array of yearly dates in A2:A20. I then have an array below it B2:B20 that the user can enter costs into. I want to be able to identify the year that the first cost appears in. The cells that do not have numbers will be left blank. How do I do this? I know I can count the occurences of cells with data: Sumproduct(--(B2:B200)) But how do I identify which column or year the first of these occurs in? Thanks . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell set to wrap text and blank line -- fix? | Excel Discussion (Misc queries) | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
conditional formatting blank cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |