Create an array with repeating values

[KonigTibor]

Imagine a table that contains issues (see the "Issue" column below) and the number of votes submitted in favor ("In favor"), against ("Against") of that issue, as well as the number of voters that were undecided ("Undecided"). I want to generate an array that represents the individual votes: a "For" means 1, an "Undecided" 0, an "Against" -1. I need this array to compute statistical values eg. median and standard deviation. So if "For" = 3, "Undecided" = 1, and "Against" = 2 then the array should be {1, 1, 1, 0, -1, -1}. What formula should I use in the "Vote Array" column of the table? No VBA please.

A sample table can be seen here:

Issue	For	Undecided	Against	Vote Array
Eternal life and free beer	3	1	2	???

 

[Hui]

Assuming results are in D3:F3

=LEFT(REPT("1,",D3)&REPT("0,",E3)&REPT("-1,",F3),LEN(REPT("1,",D3)&REPT("0,",E3)&REPT("-1,",F3))-1)

 

[bosco_yip]

If "For" = 3, "Undecided" = 1, and "Against" = 2

1] In shorten to Hui's formula, G3 enter :

=MID(REPT(",1",D3)&REPT(",0",E3)&REPT(",-1",F3),2,99)

G3, will return : 1,1,1,0,-1,-1

2] However, if you want to return an array result, G3 formula become :

=FILTERXML("<a><b>"&SUBSTITUTE(MID(REPT(",1",D3)&REPT(",0",E3)&REPT(",-1",F3),2,99),",","</b><b>")&"</b></a>","//b")

So, if you press F9 to cell G3, will give : ={1;1;1;0;-1;-1}

p.s. the FILTERXML function is available for Excel 2013 or above.

Regards
Bosco

 

[KonigTibor]

FILTERXML is nifty, thanks a lot! I was hoping for something simpler (building an in-memory XML tree seems to be overkill) but it will definitely do the job.

Is there any background material on how ranges are represented in Excel?

 

[bosco_yip]

FILTERXML is nifty, thanks a lot! I was hoping for something simpler (building an in-memory XML tree seems to be overkill) but it will definitely do the job. Is there any background material on how ranges are represented in Excel?

Or.......................

Try this simpler but longer IF function, in G3 enter :

=IF(ROW(INDIRECT("1:"&SUM(D3:F3)))<=D3,ROW(INDIRECT("1:"&SUM(D3:F3)))^0,0)+IF(ROW(INDIRECT("1:"&SUM(D3:F3)))>=(D3+E3+1),-1*ROW(INDIRECT("1:"&SUM(D3:F3)))^0,0)

p.s. please select G3 and press F9 to return the array result

Regards
Bosco

 

[Peter Bartholomew]

This is an exploration of array methods and, as it stands, is written to exploit the sequence function available in Office 365.
First I named the votes table 'CountOfVotes', What I wanted was an accumulation of the three values to set boundaries between the votes for, undecided, and against. Easily done with basic arithmetic but I chose to define a 3x3 matrix 'Accumulated' to be
= {1,1,1;0,1,1;0,0,1}
That gives the accumulated votes, in this case {3;4;6}, from the formula
= MMULT( CountOfVotes, Accumulated )

The next step is to create a sequence of values 'k' from 1 to the total of votes cast
= SEQUENCE(1, SUM(CountOfVotes))

The array of votes is created by looking up each 'k' against the accumulated votes and returning the code for each vote option
= XLOOKUP( k, MMULT( CountOfVotes, Accumulated ), Options, , 1 )

If you actually wish to see the array of votes
= Votes
will do it but it is my understanding that further processing is the goal.

The limitation of the calculation is that it will only expand the votes cast on an issue by issue basis whereas I would have preferred to return results for multiple issues as one array of arrays.