First Occurence

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have date with hours 1/1/03 0:00 (24 per day) for several weeks in column
A. Column B has 0 or 1. I want to identify in column C the first occurence
of a 1 with a 1 all other occurences with a 0. Thus in column C for any 24
hour period I would have only one 1.

I've been trying ifs and ands with day function, but still struggling.
Thanks in advance
 
Hi Mike

Assuming data in A2:B??, this formula
seems to do the trick:

in C2 enter
=IF(ROW()-ROW($C$2)+1-24*INT((ROW()-ROW($C$2))/24)=
MATCH(1,OFFSET($B$2,24*INT((ROW()-ROW($C$2))/24),0,24,1),0),1,0)

If you enter in e.g. C1, ROW($C$2) must be replaced by
ROW($C$1), because the expression acts as a counter.

Drag down with the fill handle (the little square in the
lower right corner of the cell)

In case a 24-hour period has no ones, all cells in
that period show #N/A

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Back
Top