# How to replace the zeros of an array?

#### herclau

##### Member
How to replace the zeros of an array: = {0,0,6,7}?

#### bosco_yip

##### Excel Ninja
What is your expected result ?

#### Hui

##### Excel Ninja
Staff member
=if( {0,0,6,7}<>0,{0,0,6,7}) Ctrl+Shift+Enter

#### herclau

##### Member
What is your expected result ?
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

#### 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: