Denton1234
New Member
- Joined
- Jul 3, 2017
- Messages
- 1
Hello,
I would like a formula that returns an array, that satisfies the is statement Column A = 2 - BUT I would like to skip the blanks
={N(OFFSET($B$1:$B$100,SMALL(IF($A$1:$A$100=2,ROW($B$1:$B$100)),ROW(INDIRECT("1:"&COUNTIF($B$1:$B$100,2)))),0))}
This gives me almost what I want, but it also gives me 0's when a 2 in column A matches a Blank cell in column B.
Is there a way to skip the blanks? So return an array of non 0 numbers If column A = 2
A B C
<tbody>
</tbody>
Many Thanks
Denton
I would like a formula that returns an array, that satisfies the is statement Column A = 2 - BUT I would like to skip the blanks
={N(OFFSET($B$1:$B$100,SMALL(IF($A$1:$A$100=2,ROW($B$1:$B$100)),ROW(INDIRECT("1:"&COUNTIF($B$1:$B$100,2)))),0))}
This gives me almost what I want, but it also gives me 0's when a 2 in column A matches a Blank cell in column B.
Is there a way to skip the blanks? So return an array of non 0 numbers If column A = 2
A B C
1 | ||
2 | ||
1 | ||
2 | ||
1 | ||
2 | 3.314 | 3.587 |
1 | ||
2 | 2.628 | 2.806 |
1 | ||
2 | ||
1 | 8.941 | 8.211 |
2 | 5.568 | 5.408 |
1 | 7.474 | 7.432 |
2 | 3.694 | 3.784 |
<tbody>
</tbody>
Many Thanks
Denton