Formula to calculate if cell A* is the first non-blank, then...

**galaxy_madness** · 11-24-2011, 05:52 PM

Hello,

I was hoping somebody could help me with a formula...

I have two columns A (where values may be entered) and B. I need a formula to enter in cells of column B that will return the following values:

If the cell A* is the first non-blank cell in the range A1:A100 then return a value "X" in column B of the same row, if cell A* is not the first non-blank cell then return a value "Y", and if the cell A* is blank, return blank ("").

Thanks in advance for your help!

**daddylonglegs** · 11-24-2011, 06:12 PM

Try this formula in B1 copied down

=IF(A1="","",IF(COUNTA(A$1:A1)=1,"x","y"))

**Haseeb A** · 11-24-2011, 06:13 PM

deleted...

**galaxy_madness** · 11-25-2011, 09:22 AM

Originally Posted by daddylonglegs

Try this formula in B1 copied down

=IF(A1="","",IF(COUNTA(A$1:A1)=1,"x","y"))

It doesn't seem to indentify the first entry in the range i.e. return "X", it returns "Y" for all non-blanks...

**daddylonglegs** · 11-25-2011, 01:42 PM

It should work if the "blank" cells are truly blank - do they contain formulas?

If so perhaps try this version instead

=IF(A1="","",IF(COUNTIF(A$1:A1,"<>")=1,"x","y"))

**galaxy_madness** · 11-25-2011, 02:21 PM

Originally Posted by daddylonglegs

It should work if the "blank" cells are truly blank - do they contain formulas?

If so perhaps try this version instead

=IF(A1="","",IF(COUNTIF(A$1:A1,"<>")=1,"x","y"))

This time it identifies the first entry as an "x" but when another is entered it swtiches "x" of the first entry for "y"...

Any other suggestions?

btw thanks for your help!

**daddylonglegs** · 11-25-2011, 02:34 PM

When you say "first" - I was assuming you meant positionally? Do you or do you mean "first" chronologically, i.e. the first entry you make timewise?

My suggestion works positionally, for example.....

....if you enter "apple" in A4 then B4 will display "x". If you then enter "banana" in A6 then B6 will show "y" (and B4 will still be "x"). Any further entries below A4 will be marked with a "y" too. If you enter a value in A3 now then that is now the first entry (positionally) so B3 will now be "x" and B4 will become "y".

Is that how you would like it to work?

**galaxy_madness** · 11-25-2011, 03:30 PM

Originally Posted by daddylonglegs

When you say "first" - I was assuming you meant positionally? Do you or do you mean "first" chronologically, i.e. the first entry you make timewise?

My suggestion works positionally, for example.....

....if you enter "apple" in A4 then B4 will display "x". If you then enter "banana" in A6 then B6 will show "y" (and B4 will still be "x"). Any further entries below A4 will be marked with a "y" too. If you enter a value in A3 now then that is now the first entry (positionally) so B3 will now be "x" and B4 will become "y".

Is that how you would like it to work?

My bad, it works perfectly, I must have done something wrong at first...

Thanks a bunch, your help is much appreciated!