0

I'm making a daytrading spreadsheet and I want to use formulas exclusively because I want to learn more about spreadsheet programming. I have a column called "Investment" which takes the price of a stock at the point of entry and multiplies it by the number of shares I've bought. The problem is that even though it works, it recalculates the values for the whole column every time I enter info in a new row. How can I modify my formula so that it only calculates the investment for the current row? Here's my code:

function INVESTMENT(entry, quantity) {
  var resultArray = [];

  for(row = 0; row < entry.length; row++) {
    resultArray.push(Math.abs(entry[row] * quantity[row]));
  }

  return resultArray;
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
ferengiprophet
  • 25
  • 4
  • The other cells become empty. I also get a #REF error saying: Array result was not expanded because it would overwrite data – ferengiprophet May 03 '18 at 01:46
  • if you don't want it to operate on every row, then why do you have it in a FOR loop on every 'entry' row? just invoke the 'resultArray.push()' on the current row. – Jim May 03 '18 at 01:55
  • @Jim, how do you get the current row. As for the for-loop, this is code that somebody else recommended in another post I made. – ferengiprophet May 03 '18 at 02:18
  • ferengi, this was taken from https://stackoverflow.com/questions/10966631/getting-the-cursor-row-column-from-a-google-spreadsheet-through-a-javascript-met?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa var ss = SpreadsheetApp.getActiveSpreadsheet(); Browser.msgBox("You have selected row " + ss.getActiveCell().getRow()); – Jim May 03 '18 at 02:47
  • Jim, I'll try it out – ferengiprophet May 03 '18 at 03:00
  • The whole point of the function you wanted before was to recalculate whole columns, so... what's the problem? If you don't want the formula to recalculate the whole column, then make a formula that takes a scalar argument, not an array and just copy it down the rows. Programming the spreadsheet imperatively, as suggested by @Jim, might also work but then you can't rely on automatic execution - you have to register your function to respond to some trigger/event, or execute it manually. – ttarchala May 03 '18 at 08:19

1 Answers1

0

Your function works on a RANGE so every time something in the RANGE changes the function will be recalculated.

You can rewrite your function to just work on single cells:

function INVESTMENT(entry, quantity) {
  return Math.abs(entry * quantity);
}

Then copy the formula into each row rather than just having it at the top of the results column

=INVESTMENT(A2, B2)

Calculating for a RANGE rather than a CELL can make sense in a very large Sheet, but for what you describe a simple function that works on CELLs makes more sense.

Aidan
  • 1,550
  • 1
  • 13
  • 20
  • I don't think so. ARRAYFORMULA doesn't seem to work properly with custom functions so you have to write your function to handle arrays and you are back to where you started. – Aidan May 04 '18 at 09:02
  • But since your custom formula is very simple you could just eliminate it.=ARRAYFORMULA(ABS(A2:A10*B2:B10)) works fine and doesn't recalculate all of the outputs after any change to an input. – Aidan May 04 '18 at 09:04