haruspication
New Member
- Joined
- Jan 19, 2012
- Messages
- 17
I need to include a function in my C2 cell formula that will skip days with non-zero values and then list the next non-zero day (the day is also listed N times based on Col B value).
<tbody>
</tbody>
Cell C1: Formula finds the first day (Col A) with a non-zero value (Col B) and lists it in C1.
Cell C2: Lists each day (Col A) N times based on the day's corresponding value in Col B.
=IFERROR(IF(COUNTIF($G$2:G2,G2)=INDEX(E:E,MATCH(G2,A:A,0)),IF(AND(INDEX(A:A,MATCH(G2,A:A,0)+1)<>0,INDEX(E:E,MATCH(G2,A:A,0)+1)<>0), INDEX(A:A,MATCH(G2,A:A,0)+1), IF(INDEX(E:E,MATCH(G2,A:A,0)+1)=0,INDEX(A:A,MATCH(TRUE,E:E<>0,0)), "-")),G2),"-")
The current function to skip zeros is in RED and it's wrong because it lists "Mon" repeatedly because it only finds the first day without a zero, rather than the first day without a zero based on the previous value (eg., if the last non-zero day listed in Col C is Tues, then the formula would skip Wed and Thurs and then list Fri)
A | B | C | |
1 | Mon | 2 | Mon |
2 | Tues | 1 | Mon |
3 | Wed | 0 | Tues |
4 | Thurs | 0 | Fri |
5 | Fri | 1 |
<tbody>
</tbody>
Cell C1: Formula finds the first day (Col A) with a non-zero value (Col B) and lists it in C1.
Cell C2: Lists each day (Col A) N times based on the day's corresponding value in Col B.
=IFERROR(IF(COUNTIF($G$2:G2,G2)=INDEX(E:E,MATCH(G2,A:A,0)),IF(AND(INDEX(A:A,MATCH(G2,A:A,0)+1)<>0,INDEX(E:E,MATCH(G2,A:A,0)+1)<>0), INDEX(A:A,MATCH(G2,A:A,0)+1), IF(INDEX(E:E,MATCH(G2,A:A,0)+1)=0,INDEX(A:A,MATCH(TRUE,E:E<>0,0)), "-")),G2),"-")
The current function to skip zeros is in RED and it's wrong because it lists "Mon" repeatedly because it only finds the first day without a zero, rather than the first day without a zero based on the previous value (eg., if the last non-zero day listed in Col C is Tues, then the formula would skip Wed and Thurs and then list Fri)