Q:

How to assign a 1 or a 2 based on several criteria (interesting puzzle)

I have an interesting puzzle. What I am trying to assign a 1 or a 2 based on the several criteria:

2 get assigned to the highest values
1 gets assigned to the lowest values
the 1's and the 2's have to add up to 64.

I have a data set that has 50 variables. It kind of looks like this
x    y    z
50  100
49  97
... ...
1   40

Essentially, what I am trying to do is column Z, assign a 1 or a 2. This chart is sorted so the values in Y are from highest to lowest. I need to assign a 2 to the highest values in column Y (and 1 for the lowest values). However, I need the 1's and 2's to sum up to 64. 
I have been stuck for a while on this one. Any help would be greatly appreciated.
Thanks,
G

A:

There have to be 14 "2"s and 36 "1"s. 
You could rephrase your problem this way:

x is the number of rows assigned a 2
y is the number of rows assigned a 1
2x + y = 64
x + y = 50
Solve for x and y.

A:

This is easy with a macro.
Begin by assigning 1 to every item.  Then, starting with the highest value and working your way down, add 1 to the items until the total reaches 64.
Sub assign_values()
    Range("C1:C50").Value = 1
    For i = 1 To 50
        If Application.WorksheetFunction.Sum(Range("C:C")) = 64 Then Exit Sub
        Cells(i, "C").Value = Cells(i, "C").Value + 1
    Next i
End Sub

Note that my data has been sorted high-to-low and the code takes advantage of that.

