Removing zero's from a formula array then using this array

[Dosnox]

HI All,

I had a look around and couldn't figure out how to do this. I'm working on some legacy spreadsheets and updating the formula's to be more dynamic rather than fixed to accommodate when new data is added.

I'm having a bit of trouble showing what my dummy data table looks looks like but it sort of looks like this

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]No of Weekdays[/TD]
[TD]Total Days[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Week 1[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Week 2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Week 1[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Week 2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]


In another cell I've currently got the array formula below where my E1 has the value 'Week 1'

={SUMPRODUCT(IF($C$2:$C$6>0,($B$2:$B$6/C$2:$C$6)*($A$2:$A$6=E1),0))}

as part of the calculation the array is returned below

{0.714285714285714;0;0;0;0;0;0;0;0;0.428571428571429;0;0;0;0}

I'd like it to return the array below(exlcudes the zero's) instead as I want to multiply it by another array of size 2

{0.714285714285714;0.428571428571429;}

Hopefully this makes sense, let me know if it requires clarification and thanks for your help!

Thanks,
Shan

 

[DRSteele]

I don't know how you produced the array

{0.714285714285714;0;0;0;0;0;0;0;0;0.428571428571429;0;0;0;0}

because your SUMPRODUCT formula sums the results of a much shorter array,

{0.714285714285714;0;0;0.428571428571429;0}

In any event, here is one way to contract an array. It reports an array of the values that are not equal to the value you wish to exclude (0, here). To do so, it first ascertains how many of them are not 0 (which is 2 here). Then it uses INDIRECT and ROW to create another array from 1 to that figure (2), which is then used as the K argument, {1:2}, inside AGRREGATE, which reports an array of the K largest values (2) in the array in question. If you put this in edit mode and press f9 to evaluate, the equation will report {0.714285714285714;0.428571428571429}

=AGGREGATE(14,6,{0.714285714285714;0;0;0;0;0;0;0;0;0.428571428571429;0;0;0;0},ROW(INDIRECT("1:"&SUM(--({0.714285714285714;0;0;0;0;0;0;0;0;0.428571428571429;0;0;0;0}<>0)))))

 

[Dosnox]

Apologies DRSteele, when I originally posted the question, I had more blank rows hence the larger array. But thanks for this solution it's worked perfectly for me, never used Aggregate before, really creative solution.

Thanks,
Shan

 

[DRSteele]

No apologies necessary.

You're welcome. I'm glad it worked out for you.

Check out this video playlist and other videos about AGGREGATE from the terrific ExcelIsFun channel on youtube.
Excel 2010 Magic Trick 661: AGGREGATE function Contains 19 different functions - YouTube