Google-sheets – Divide data evenly in row, place in additional rows

formulasgoogle sheets

I am using Goodle Sheets, and I have a simple problem. I am looking to divide the data by 4 in many rows, and place the results in new separate rows, replacing the original row. The new rows should replace the original, and now there should be 4 identical rows.

So for every 1 original row, we get 4 identical rows, each is 1/4th of the original totals.

It would probably be easiest to put the new results on a new sheet. This is a one time task, so it doesn't need to be fully automated. There are only 200 rows, so I can go through and do the spacing myself to prep if needed. Some rows have words or other static data, so we're only doing this to some columns.

Here's an example:
Original:

80  24  38  16  42  George
160  32  46  10  62  Mary

Becomes this:

20  6  9.5  4     10.5  George
20  6  9.5  4     10.5  George
20  6  9.5  4     10.5  George
20  6  9.5  4     10.5  George
40  8  11.5  2.5  15.5  Mary
40  8  11.5  2.5  15.5  Mary
40  8  11.5  2.5  15.5  Mary
40  8  11.5  2.5  15.5  Mary

((Bonus Points: Ideally I would like to divide by a random number between 4 and 7, and have the results print in the same random number of lines. The columns must always add up to the original values, so we're not changing anything, only splitting it up. So 10 rows becomes 40 rows of the same data. This might complicate things so the random number idea is not necessary.))

Thank you very much for your time and help! I really appreciate it!

Best Answer

Okay, so what I have presented is a random divisor for every number. If you want all the rows in a column to add up, it will not be a random number. for instance, if you want to have 4 rows, the number in which you will use as a divisor will be 4. This is unless you are referring to the average of the random divisors to equal 4... or however that really works. The point is, choosing a random number between 4 and 7 will not have the summation results in which you are looking for.

    function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = ss.getDataRange().getValues();
  var newData = [[,,,,,]];
  var i = 0;
  var j = 0;
  var k = 1;
  var random = [];
  for (var i = 0, dataLength = data.length; i < dataLength; i++) {
    for (k = 1; k < 6; k++) {
    random[k] = Math.floor(Math.random() * 7) + 4;
    }
    newData[j] = [data[i][0] / random[1],data[i][1] / random[2],data[i][2] / random[3],data[i][3] / random[4],data[i][4] / random[5],data[i][5]];
    for (k = 1; k < 6; k++) {
      random[k] = Math.floor(Math.random() * 7) + 4;
    }
    newData[j+1] = [data[i][0] / random[1],data[i][1] / random[2],data[i][2] / random[3],data[i][3] / random[4],data[i][4] / random[5],data[i][5]];
    for (k = 1; k < 6; k++) {
      random[k] = Math.floor(Math.random() * 7) + 4;
    }
    newData[j+2] = [data[i][0] / random[1],data[i][1] / random[2],data[i][2] / random[3],data[i][3] / random[4],data[i][4] / random[5],data[i][5]];
    for (k = 1; k < 6; k++) {
      random[k] = Math.floor(Math.random() * 7) + 4;
    }
    newData[j+3] = [data[i][0] / random[1],data[i][1] / random[2],data[i][2] / random[3],data[i][3] / random[4],data[i][4] / random[5],data[i][5]];
    j = j + 4
  }
  ss.getRange(1,1, newData.length,6).setValues(newData);
}
Related Topic