How can I have a number calculate the difference between its current value and the new value I am entering, and store the difference in another cell?

It can be done with a script. If you turn it into a Service (now called a Quick Action in Automator) and if you assign it a keyboard short and if you allow Numbers to control your computer (to allow a keystoke command in the script to work), it can be done. I wrote one below that gets the job done though I'm sure it could be written better.

You can try this script in the Script Editor app first. After you have it in Script Editor, select a cell in a Numbers table, type a number into it but do not hit Return, go back to Script Editor and hit the Play button. The new number should get entered into the cell and the difference entered into the cell next to it.

tell application "Numbers" to tell front document
	activate application "Numbers"
	tell active sheet
		tell (first table whose class of selection range is range)
			if value of first cell in the selection range is not missing value then
				set oldvalue to value of first cell in the selection range
			else
				set oldvalue to 0
			end if
			set therow to row of first cell in the selection range
			set thecoladdr to address of column of first cell in the selection range
			tell application "System Events" to key code 76
			set newvalue to value of cell thecoladdr of therow
			set value of cell (thecoladdr + 1) of therow to (newvalue - oldvalue)
			set selection range to cell thecoladdr of therow
		end tell
	end tell
end tell

To use it as you want to use it,

1. Start up the Automator App.
2. Create a new Quick Action that takes "no input" "in application Numbers"
3. Drag over the Run Applescript action from the Utilities folder of the Library
4. Delete the contents and replace with the script
5. Save it with a descriptive name
6. Check the Numbers/Services menu to see that it is now there.
7. Open System Preferences/Security/Privacy/Accessibility and click the checkbox for Numbers. It appears you have to do this so the workflow can do the keypress in the script.
8. Go to System Preferences/Keyboard/Shortcuts/App Shortcuts and add a shortcut for this new service. Type the name exactly as it appears in the Numbers/Services menu. I used the shortcut Ctrl Shift Return in my test.

To use it, type a number into a cell in a table but do not hit Enter, use your shortcut to run the service which will enter the number in the cell and enter the difference in the cell next to it.

EDIT: If you saw this right after I first posted it, I have edited the script to handle data entry into a currently blank cell. It would fail if the cell did not have a number in it to start with.

johnnylundy wrote:

For example, a cell contains “344.90”. I enter “345.10” in the cell. I want the cell next to it to now show “0.20” .

if I enter “344.80”, I want the other cell to now show “-0.10”.

Can this be done?

Not directly. A Numbers cell has no "memory" of its previous values. You need at least three cells for this, one for the original value, one for the new value, and one to calculation the difference between the new and the old value.

In D2:

=C2-B2

SG

johnnylundy wrote:

Right, but now I need B2 to take on its new value (i.e. 345.10 . ..what I WANT is a self-reference.

But you can't have a self-reference. You can, however, do something like this:

Where. the first row is the first time period or whatever, and the second row is the next period, and so on down the table.

The formula in B3 retrieves the new value from the previous row.

SG

"Can this be done?"

No.

When you placed the value in the first cell, that value replaced whatever was there before.

When you placed a different value in the second cell, that value replaced whatever was there before.

Changing the value in the first cell (without actually entering the new value from the keyboard) would require a formula in the first cell.

Entering a formula into a cell replaces whatever was in that cell—in this case, the original number.

To detrmine the difference, the formula would have to subtract the original number contained in its own cell (which no longer exists) from the number placed in the second cell.

This is known as a circular reference, or a self reference, and is not permitted in Numbers.

You could, however, enter the data in a separate column, and write the formula (in a separate cell) to find the difference between the last and second last values in that list.

Using a series of formulas that subtract the value in 'this row' from the value in the 'previous row' of the data list would then display the difference at each change.

List in column B, starting at B2

Formula in C3: =B3-B2

Fill the formula down to the end of th e column.

Sme formula, with a 'switch' added to prevent calculation until a value ins entered in its row:

C3: IF(B3="",""",B3-B2)

Regards,

Barry

johnnylundy wrote:

I can script it, but there is no way I know of to trigger a script on entry of data in a cell.

I wonder if Excel has basic “do this when this cell changes” or “x=x+1” like formulae allowed?

In Numbers there is no (practical) way of triggering a script when entering data in a cell. In general an AppleScript must be triggered by clicking a button, making a choice from the menu, or typing a keyboard shortcut combination.

Using VBA in Excel may allow you to do what you want. Excel can even be configured to allow circular references. But that is not an easy solution to implement.

As you are probably aware spreadsheets in general aren't ideally suited to track stocks or a portfolio of stocks in near real time. Setting them up properly to do that gets complicated. That's why it is usually better to adopt specialized software for that purpose.

SG