I have a datasheet with columns which contain 1's and 0's. I need to identify any incidence where there are either 5 or more 1's consecutively and leave them as 1. If there are any 1's where there are less than 5 consecutively i need to change them to 0, and if there are more than 1400 1's consecutively i need to change them to 'NA'.
I've tried:
=IF((SUM(A1:A5)>=5),1,0) - but this excludes numbers i need to keep as 1
I thought i could then use an aggregate formula and then change anything more than 5 to a 1
=AGGREGATE(9,6,A1:A5) -but again this doesn't do what i need
I thought maybe i could use an array but i'm not sure where to start. Is there a simple solution to this?
Thanks
Bookmarks