Remove 0 from array formula

[James006]

Hi,

Is there a trick to remove all 0 values from any given array formula ?

Since my objective is to transpose back to the sheet only valid values ...

Cheers

 

[James006]

I am guessing that Barry or Aladin could just let me know if it is feasible or not ...

Thanks in advance

 

[sous2817]

can you post the formula?

you could just add in a <>0 condition.

 

[James006]

Re: Remove 0 from Array Formula

As a matter of posting an example, say there are two dates in cells A2 and A4 ...the following array formula produces the result I expect .. but with a couple of zero values I would like to eliminate ...

=ROW(INDIRECT($A2&":"&$A4))*IF(WEEKDAY(ROW(INDIRECT($A2&":"&$A4)))<=6,1,0)

Cheers

 

[James006]

Adding a condition only transforms elements of array formula into True/False but I still cannot eliminate all 0 values ...

Hope some MVP will stop by ..

 

[James006]

Still battling with elimination of 0 values in an array formula ... my attempts with Max() and Min() functions are not producing the expected result ...

Arrrr ...

 

[Aladin Akyurek]

Still battling with elimination of 0 values in an array formula ... my attempts with Max() and Min() functions are not producing the expected result ...

Arrrr ...

What is the intent? That is, what are you intending to do with the resulting array?

 

[James006]

Aladin,

Thanks a lot ...

My intent is to transpose back to the worksheet the working days during a given period of time ... but with a twist : i.e including and excluding a variable week day (depending on some circumstances ..)

For example, the initial attempt is to list all working days ... including Saturdays and excluding Sundays ...

=ROW(INDIRECT($A2&":"&$A3))*IF(WEEKDAY(ROW(INDIRECT($A2&":"&$A3)))<=6,1,0)

Thanks a lot for your kind assistance

Cheers

 

[Aladin Akyurek]

Aladin,

Thanks a lot ...

My intent is to transpose back to the worksheet the working days during a given period of time ... but with a twist : i.e including and excluding a variable week day (depending on some circumstances ..)

For example, the initial attempt is to list all working days ... including Saturdays and excluding Sundays ...

=ROW(INDIRECT($A2&":"&$A3))*IF(WEEKDAY(ROW(INDIRECT($A2&":"&$A3)))<=6,1,0)

Thanks a lot for your kind assistance

Cheers

If you intend to create a range, consider:

<TABLE style="WIDTH: 52pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=69><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2446" width=69><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=69 align=right>6</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>1/29/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>2/8/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>9</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>1/30/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>1/31/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>2/1/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>2/2/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>2/3/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>2/4/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>2/6/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>2/7/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>2/8/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19> </TD></TR></TBODY></TABLE>

A1: 6

A2: 1/29/2011 (start date)

A3: 2/8/2011 (end date)

A4, control+shift+enter, not just enter:

=SUM(IF(WEEKDAY(ROW(INDIRECT($A$2&":"&$A$3)))<=$A$1,1))

A5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$5:A5)<=$A$4,SMALL(ROW(INDIRECT($A$2&":"&$A$3))*
    IF(WEEKDAY(ROW(INDIRECT($A$2&":"&$A$3)))<=6,1,0),
      ROWS($A$5:A5)+($A$3-$A$2+1-$A$4)),"")

 

[James006]

Aladin ...

Absolutely ... Fantastically ... Brilliant ...

Many many thanks ... for this beautiful lesson ...

And Thanks again for sharing your wonderful talent and expertise ...

with ... poor mortals ...

Cheers