• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to replace the zeros of an array?

bosco_yip

Excel Ninja
It could be = {6,7} or {1000,1000,7,6}
Then with the result I would like to implement the following line:
Small ({1000,1000,7,6}, 1}) => 6
Small ({1000,1000,7,6}, 2}) => 7
Maybe,

=AGGREGATE(15,6,{0,0,6,7}/({0,0,6,7}>0),ROW(INDIRECT("1:"&SUMPRODUCT(0+({0,0,6,7}>0))))) ==> return {6;7}
=AGGREGATE(15,6,{0,0,6,7}/({0,0,6,7}>0),1) ==> return 6
=AGGREGATE(15,6,{0,0,6,7}/({0,0,6,7}>0),2) ==> return 7

or,

=0+TEXT({0,0,6,7},"0;;1000;") ==> return {1000,1000,6,7}
=SMALL(0+TEXT({0,0,6,7},"0;;1000;"),1) ==> return 6
=SMALL(0+TEXT({0,0,6,7},"0;;1000;"),2) ==> return 7

Regards
Bosco
 
Last edited:
Top