I need a formula for the below!!! Help!
Basically, I want excel to look at the last week of data, and if the cell is 0, count back how many times it was consecutively 0.
Ex.
Row 1: 0 3 3 0 answer: 1
Row 2: 0 0 0 0 answer: 4
Row 3: 0 1 0 4 answer: 0
Row 4: 5 -1 0 0 answer: 2
The simplier the formula the better. I want to be able to use this formula for any timeframe I pull.
Thank you!
ex.JPG
pls attach sample excel file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Assuming no empty cells...
Data Range
This array formula** entered in F2 and copied down:
=IFERROR(COLUMNS(A2:E2)-MATCH(2,1/(A2:E2<>0)),COLUMNS(A2:E2))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
=column()-1-sumproduct(max((a1:e1<>0)*column(a1:e1)))
That formula is dependent upon what cell it's entered in.Originally Posted by nflsales
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks