• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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:

IssueForUndecidedAgainstVote Array
Eternal life and free beer312???
 
Last edited:

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?
63833

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
= 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.
 

Attachments

Top