Sum 16 randomly selected cells out of 32

[TAPALMA]

Dear all,

I did a search for this question and found many threads where people asked for questions related random selection of cells, however, none was really helpful for my case. Perhaps because I don't know macros very well. Anyway, here my problem:


I have an excel database with 61 rows and 32 columns. Each cell has a 0 or a 1. For each row, I would like to obtain two sums, one resulting from the random selection of 16 cells and the other with the remaining 16 cells. Or, I can revert the database, and think of it the other way around -32 rows and 61 columns; for each column, I would like to obtain to new values: one resulting from the sum of 16 randomly selected cells and the from the remaining 16 cells.


Does anyone know how to do something like this?


Thank you very much!
Best,
Tomás

 

[crazydragon84]

i don't understand the question. So basically you have 16 x 32 that either has 0 or 1. You want to add a 17th column that adds the 16 rows + 16 random cells within 16 x 32? for the random cell part, if everythin in 16 x 32 is only 0 or 1, you don't need to select anything, just do Randbetween(0,16), this will give you a number between 0 -16 (this is assuming your 0s and 1s are random, if your 0s and 1s are not random then this doesn't work). let me know if that helps.

 

[TAPALMA]

Hi crazydragon84,

Sorry if my question wasn't clear. Let's ignore the content of the cells (0's and 1's). I have 61 rows and 32 columns. I want to create 2 new columns - AF and AG. In AF1, I would like to have the sum of 16 randomly selected column cells while in AG1 I want the sum of the remaining 16 column cells. Then I want to repeat this for all the other rows. Is it clear now?

Thanks

 

[Eric W]

If you have the BASE function (Excel 2013 or newer), then try this:

AG1: =SUMPRODUCT(A1:AF1,MID(BASE(RANDBETWEEN(0,2^32-1),2,32),COLUMN(A1:AF1),1)+0)
AH1: =SUM(A1:AF1)-AG1

AF is the 32nd column.

 

[Eric W]

If you don't have BASE, then:

=SUMPRODUCT(A1:AF1,IF(ISODD(INT(RANDBETWEEN(0,2^32-1)/2^(COLUMN(A1:AF1)-1))),1))

confirmed with Control+Shift+Enter.

 

[wideboydixon]

I love this solution but I fear it doesn't pick exactly 16 columns; there's no way to guarantee that the random number when converted to binary will have 16 zeros and 16 ones. It's very tricky with a formula I think. I had a solution using helper columns like this:

In cells AI1:BN1 put the following formula:

=RAND()

In cell BO1 put the following formula:

=RANK(AI1,$AI1:$BN1)

Copy cell BO1 and copy it all the way across to CT1. The formula in AG1 is then:

=SUMPRODUCT(--($BO1:$CT1<17),$A1:$AF1)

And in AH1 we have:

=SUM($A1:$AF1)-$AG1

It's not very nice and it will recalculate all the time because of the RAND() volatile functions.

WBD

 

[TAPALMA]

Hi Eric W,

Thanks a lot for your help. I have Excel 16 for Mac but it's not working. It gives this very weird message: [FONT=&quot]
[/FONT]
[FONT=&quot]Not trying to type a formula?[/FONT]
[FONT=&quot]When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]• you type: =1+1, cell shows: 2[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]To get around this, type an apostrophe ( ' ) first:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]• you type: '=1+1, cell shows: =1+1


[/FONT]
Any idea of what might be happening?

Thanks a lot!

 

[Eric W]

Ugh! WBD, of course you're right! Don't know how I missed that. Your solution should work better. I'll have to cogitate about another way to do it, but nothing jumps to mind. I can think of another multi-cell version, but nothing better than what you had.

TAPALMA, try the formulas from WBD. If I think of something better, I'll let you know. If you want something non-volatile, you'll need VBA.

 

[wideboydixon]

Here's some code that will do it without having the volatility:

Public Sub SumRandomColumns()

Dim lastRow As Long
Dim thisRow As Long
Dim thisCol As Long
Dim colCount As Long
Dim runningTotal As Long
Dim usedColumn(31) As Byte

Randomize Now
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For thisRow = 1 To lastRow
    Erase usedColumn
    runningTotal = 0
    For colCount = 1 To 16
        Do
            thisCol = Int(Rnd() * 32)
        Loop Until usedColumn(thisCol) = 0
        usedColumn(thisCol) = 1
        runningTotal = runningTotal + Cells(thisRow, thisCol + 1).Value
    Next colCount
    Cells(thisRow, 33).Value = runningTotal
    Cells(thisRow, 34).Value = WorksheetFunction.Sum(Range(Cells(thisRow, 1), Cells(thisRow, 32))) - Cells(thisRow, 33).Value
Next thisRow

End Sub

WBD

 

[TAPALMA]

Hi wideboydixon,

I tried your other formula and it works great! Will try this last one too.

Thanks a lot!
Best,
Tomás