# Create an array with repeating values

#### KonigTibor

##### New Member
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 ???

Last edited:

#### Hui

##### Excel Ninja
Staff member
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

##### Excel Ninja
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

Last edited:

#### KonigTibor

##### New Member
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

##### Excel Ninja
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

##### Well-Known Member
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

The next step is to create a sequence of values 'k' from 1 to the total of votes cast

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