mardi 18 février 2020

Dynamic Split Equity Calculations Google Sheets second Issue

I posted another issue a day ago, but this being a new set of problems, I thought I'd post a new topic. I have gotten the sheet to do almost everything I want. I want it to take an incoming dollar value, divide it into percents that are declared in a specific cell, and display those values in cells adjacent to where I place the initial value. The catch is that each partner in the split is only owed a certain amount. When their amount due reaches zero, the amount that they would have been paid gets evenly distributed among the other partners until their balances are paid. If two partners reach a 0 balance, their portions of the split are evenly distributed, and so on until all partners reach zero. In this case, there are three (and will probably only ever be three) partners. Here is my code so far:

/**
* Creates a Date Stamp if a column is edited.
* Also performs dynamic split calculations
*/

//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 13;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,-1];
// Where the calculations will be performed for the Partners.
var PARTNERONELOCATION = [0, 1];
var PARTNERTWOLOCATION = [0, 2];
var PARTNERTHREELOCATION = [0, 3];
// Sheet you are working on
var SHEETNAME1 = 'Sheet1'

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var partnerOnePercent = sheet.getRange("G2").getValue();
  var partnerOnePaySource = ss.getActiveCell().getValue();
  var partnerTwoPercent = sheet.getRange("G3").getValue();
  var partnerTwoPaySource = ss.getActiveCell().getValue();
  var partnerThreePercent = sheet.getRange("G4").getValue();
  var partnerThreePaySource = ss.getActiveCell().getValue();
  //checks that we're on the correct sheet.

  if(sheet.getSheetName() == SHEETNAME1 ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date and calculations to appear.

    if( selectedCell.getColumn() == COLUMNTOCHECK) {
      var partnerOneAmountDue = sheet.getRange("J2").getValue();
      var partnerTwoAmountDue = sheet.getRange("J3").getValue();
      var partnerThreeAmountDue = sheet.getRange("J4").getValue();
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      var partnerOneCell = selectedCell.offset(PARTNERONELOCATION[0],PARTNERONELOCATION[1]);
      partnerOneCell.setValue((partnerOnePercent/100)*partnerOnePaySource);
      var partnerTwoCell = selectedCell.offset(PARTNERTWOLOCATION[0],PARTNERTWOLOCATION[1]);
      partnerTwoCell.setValue((partnerTwoPercent/100)*partnerTwoPaySource);
      var partnerThreeCell = selectedCell.offset(PARTNERTHREELOCATION[0],PARTNERTHREELOCATION[1]);
      partnerThreeCell.setValue((partnerThreePercent/100)*partnerThreePaySource);
      var partnerOnePay = selectedCell.offset(PARTNERONELOCATION[0],PARTNERONELOCATION[1]).getValue();
        var partnerOneDifference = (partnerOnePay-partnerOneAmountDue);
        var partnerOneHalf = (partnerOneDifference/2);
      var partnerTwoPay = selectedCell.offset(PARTNERTWOLOCATION[0],PARTNERTWOLOCATION[1]).getValue();
        var partnerTwoDifference = (partnerTwoPay-partnerTwoAmountDue);
        var partnerTwoHalf = (partnerTwoDifference/2);
      var partnerThreePay = selectedCell.offset(PARTNERTHREELOCATION[0],PARTNERTHREELOCATION[1]).getValue();
        var partnerThreeDifference = (partnerThreePay-partnerThreeAmountDue);
        var partnerThreeHalf = (partnerThreeDifference/2);

      if(partnerOnePay > partnerOneAmountDue) {
        partnerOneCell.setValue(partnerOneAmountDue);
        partnerTwoCell.setValue(partnerTwoPay+partnerOneHalf);
        partnerThreeCell.setValue(partnerThreePay+partnerOneHalf);
        if(partnerTwoPay > partnerTwoAmountDue) {
          partnerTwoCell.setValue(partnerTwoAmountDue);
          partnerThreeCell.setValue(partnerThreePay+(partnerTwoHalf*2)+(partnerOneHalf*2));
        }
        if(partnerThreePay > partnerThreeAmountDue) {
          partnerThreeCell.setValue(partnerThreeAmountDue);
          partnerTwoCell.setValue(partnerTwoPay+(partnerThreeHalf*2)+(partnerOneHalf*2));
        }
      }

      if(partnerTwoPay > partnerTwoAmountDue) {
        partnerTwoCell.setValue(partnerTwoAmountDue);
        partnerOneCell.setValue(partnerOnePay+partnerTwoHalf);
        partnerThreeCell.setValue(partnerThreePay+partnerTwoHalf);
        if(partnerOnePay > partnerOneAmountDue){
          partnerOneCell.setValue(partnerOneAmountDue);
          partnerThreeCell.setValue(partnerThreePay+(partnerOneHalf*2)+(partnerTwoHalf*2));
        }
        if(partnerThreePay > partnerThreeAmountDue) {
          partnerThreePay.setValue(partnerThreeAmountDue);
          partnerOneCell.setValue(partnerOnePay+(partnerThreeHalf*2)+(partnerTwoHalf*2));
        }
      }

      if(partnerThreePay > partnerThreeAmountDue) {
        partnerThreeCell.setValue(partnerThreeAmountDue);
        partnerTwoCell.setValue(partnerTwoPay+partnerThreeHalf);
        partnerOneCell.setValue(partnerOnePay+partnerOneHalf);
        if(partnerOnePay > partnerOneAmountDue) {
          partnerOneCell.setValue(partnerOneAmountDue);
          partnerTwoCell.setValue(partnerTwoPay+(partnerOneHalf*2)+(partnerThreeHalf*2));
        }
        if(partnerTwoPay > partnerTwoAmountDue) {
          partnerTwoCell.setValue(partnerTwoAmountDue);
          partnerOneCell.setValue(partnerOnePay+(partnerTwoHalf*2)+(partnerThreeHalf*2));
        }
      }
    }
  }
}

This code does the initial dynamic split great. It can grab the first zero balance a split it up perfectly every time. However, when we get to two partners at zero due there is a flaw in the logic that I can't find. It simply does not perform the second split and tries to keep paying the partner at zero due. I've tried utilizing some getValue methods and some simple logical expressions to change some of the variables, but nothing seems to work. Help?

Aucun commentaire:

Enregistrer un commentaire