I have a column of numbers. How do I multiply each cell by the number -1?

ngershon wrote:

I’d like to have all the cells in the column multiplied by -1, in one or just a few actions.

Sure. That is easy to do in Excel without formulas. It is easy in Numbers too!

I just select the cells I want to change, and in the Shortcuts menu in the Menu Bar choose 'Multiply Values in Cells by ...'

Answer the prompt:

And voila!

To view in the built-in Shortcuts app and install it just click this link in Safari:

https://www.icloud.com/shortcuts/90a974fce4ae4f56978559d86e5c6bd6

Then pin it to the Menu Bar.

As you can see the Shortcut contains a simple AppleScript action. To use the Shortcut you don't have to understand the script, or even look at it again, if you don't want to. You can just treat it as a new custom menu item for Numbers.

SG

SGIII, I hope you don't mind if I extended your script a little so the math operation to be performed (+-*/) can be specified.

set validops to ["+", "-", "*", "/"]

set factor to (text returned of (display dialog "Enter Math Operator +-*/ and Value" default answer "*-1"))

set mathop to first character of factor
if mathop is not in validops then
	display dialog "Math Operator must be + - * or /"
	return
end if

set factor to text 2 thru -1 of factor

tell application "Numbers"
	tell front document's active sheet
		tell (first table whose selection range's class is range)
			set selRange to selection range
			repeat with c in selRange's cells
				try
					if mathop = "*" then
						set newVal to ((c's value) * factor)
					else if mathop = "/" then
						set newVal to ((c's value) / factor)
					else if mathop = "+" then
						set newVal to ((c's value) + factor)
					else if mathop = "-" then
						set newVal to ((c's value) - factor)
					else
						set newVal to (c's value) --this should never happen
					end if
					set c's value to newVal
				end try
			end repeat
		end tell
	end tell
end tell

https://www.icloud.com/shortcuts/45f918bd569b4f5bb88ff0a73085d8bd 

I note that it will not change date&time values and it treats durations as seconds with the result being the number of seconds as a number with the duration format removed.

"Here is a better wording of what I want…"

I’d like to have all the cells in the column multiplied by -1, in one or just a few actions. It would be too much trouble to do each cell individually which I know how to do and which is what i think you meant."

This sounds like you want to, for example,

multiply the values in cell B3 and other cells in column B by -1,and display the result in cell B3 and other cells originally containing the values to be multiplied.

Not possible. A cell can contain an entered value, or can contain a formula that calculates a value using values written into the formula and/or values retrieved by references to cells other than the one containing the formula. 'Self reference' to the cell containing the formula is not allowed.

ghr165's solution recognizes that limitation.

To calculate and display the result of multiplying the values in say, column B, you need to place a formula in the cells in a different column, and in rows corresponding to those contain ing the original values in column B.

Simplest arrangement is to enter the formula below in column C (for example), starting with the row containing the first value to be converted in column B.

For the example, we'll assume the column B values are in cells B2:B10, and will use cells in those rows of column C to contain the conversion formula.

If you want to replace the original values in Column B, you can, as ghr… has suggested, select then Copy the values in column C, click on cell B2 to select it, then go to the Edit menu and choose Paste Formula Results.

This command strips the formul from the last results calculated, and pastes those results (as fixed values) into the selected cells.

In the example, I selected cells in column D as I did not want to over-write the formulas in column B. In your case, where replacing the results/values in column B with their *-1 counterparts is the desired end result, you would select cell B2 then do the paste formula results in that column, erasing the original results and replacing them with the negative twin of that value.

Regards,

Barry

Put formula in cell eg B2 * -1

Grab the bottom of the cell (yellow dot) and drag down to the last cell. Or define the cells and choose Table > AutoFill Cells > AutoFill Down

The cells should fill with correct values (eg -1 to -4)

Press command-C to copy the cells

Click on the first original cell and choose Edit > Paste Formula Results

Delete the unneeded column

Use a formula in cells in another column and then copy and paste the values over the original column

My 'solution' is not for each cell individually. It is intended for the whole column.

Let me know if you are not aware of the 'AutoFill' action which allows you to copy a formula from one cell into a range of cells.

Excuse us

We didn't realize that there was a minus.

Or rather, We guess he thought it was a hyphen. That was why We had a question.

Thank you.

Retracted.

Thanks, Badunit, for a Cadillac version!

SG