changing a column of positive numbers to negative

Assuming the numbers in column E are not created by a formula,

1. Add a new column to the right. It will be column F
2. In column F in the first row that has data, put the formula =-E
3. Select that cell.
4. Copy
5. Select the rest of the cells in column F where you want negative numbers
6. Paste. The column should be full of negative numbers now.
7. Select all the cells in column F that have your negative numbers.
8. Copy
9. Select the first cell in column E that has your positive numbers
10. Paste Values (found in the Edit menu). This is not the same as the normal "Paste" command
11. Delete the column you added in the first step.

It would simpler if you could just select the cell(s) and then be able to have a selection to convert the data from/to positive/negative as you have to convert numbers to currency. Simple

Barry,

It's been a decade or more since I used Excel, but I think Paste Special > Multiply, in Excel is quite a bit easier. It's one of those many little gimmicks that are nice, but that we can easily do without. I don't know if the feature is the same as it was back then, but if Libre Office is true to the current Excel, it goes like this...

To do this operaion in Excel, we would Copy a cell with -1 in it and Paste Special, Multiply into a selection that we want to change the sign of. It takes no auxiliary column, just one extra working cell to pick up the multiplier from.

Jerry

I can't get it to compile for Numbers '09.

Numbers '09 doesn't recognize active sheet. That problem had me stumped until Hiroto explained in this thread how to work around that (well worth a read because he also illustrates some other useful scripting).

So the following should compile and work in Numbers '09:

--select cells in Numbers '09, run; converts positive numbers to negative; and vice versa

--Numbers 3 must not be open at the same time

property myMultiplier : -1

tell application "Numbers" to tell the front document to tell (sheet 1 whose every table's selection range's class contains range) to tell (first table whose class of selection range is range)

tell the selection range to repeat with i from 1 to count cells

tell cell i

try

if its value is not 0 then set its value to (its value) * myMultiplier

end try

end tell

end repeat

end tell

--end of script

Note that this time you have to test for 0 because Numbers 2.3 happily treats a blank cell as 0, whereas Numbers 3 (more correctly, I think) treats the value of a blank cell asmissing value, thus throwing off an error if you try multiply it by a number.

I think the AppleScript imlementation in Numbers 3.1 is better, with one exception. In Numbers 2.3 you can access an existing formula by forcing the format to text and then getting the value. In Numbers 3.1 I haven't found a way to access a formula (though it is straightforward to construct new formulas and insert them in cells).

In any case, I've found scripting the selected range much simpler than many examples from the past make it seem. Solving the problem posed in this thread is surprisingly convenient with AppleScript.

SG

"I was answering Barry who had asked how the process could be made simpler than it is."

Actually, I asked "How would…", which Jerry's reply answered.

The method, as decribed by Jerry:

1. select a cell containing the multiplier value—or enter the multipler value into a cell
2. copy
3. select the range of cells whose content is to be modified
4. Go Edit > Paste Special > Multiply

Definitely simpler if it's 'what I'm used to doing.' Not quite as simple as using either of the scripts since provided. If I needed to do this regularly (especially if the conversion was always reversing the sign), I think I'd go for one of those scripts, saved as a Service, and assign it a keyboard shortcut.

Also, if you think it would be a worthwhile addition to Numbers's talents, use Provide Numbers Feedback, found in the Numbers menu, to make a feature request for a future edition of Numbers.

Regards,

Barry

Just found this. Brilliant! Why Apple couldn't have a simple convert option built in I don't know but this is a useful workaround.

Thanks again!

Hi makfai,

Numbers does not have this convenient convert option. Numbers is not Excel, and I hope never will be 😉.

Changing the cell data format (numbers to currency or text or something else) does not change the value. It changes how the value is displayed in the cell, and how it behaves in formulas. Changing positive to negative changes the value.

Regards,

Ian.

Message was edited by: Yellowbox. added: and how it behaves in formulas.

I have never suggested that Numbers should be the same as Excel.

As regards the distinction you made I agree there is a value change but that was not the point I was making.

I was answering Barry who had asked how the process could be made simpler than it is.

What I was describing by making the comparison with selecting currency was the interface with an end-user which could be used. I was not saying that the process was identical.

The whole point is that we have moved on. I am old enough to have had the first Apples when we first lived in the Garden of Eden (DOS); when a mouse lived under the floorboards and was pursued by a cat!

The whole idea of having auto-functions selected from a menu was Apple's and it was so we did not have to jump thorugh hoops to do something. It utilised the powere of the computer to do it for us. The computer did the jumping and we just told it what we wanted to achieve. That is all I am saying here.

It would be simple to write the code to do whatwe we now have to do manually: create a column >create a formul >insert a formula.copy and paste etc etc. We have done it for more complicated issues.

Hi Makfai,

Point taken, and no offence intended. I too came out of the Ark and used AppleScript on the first Apples (we needed to plug into a television screen to see the display). Mac Plus and Mac SE in the early 1980s (swapping floppy discs in Toaster Mode). Mac Classic in the 1990s (still no colour display). I also played with DOS, a good OS once you got the hang of how to use Help. Word Perfect app with no mouse but we used function keys. Microsoft Works on Windows 3.1 was the closest thing to a Mac interface at the time. HyperCard, MacDraw, MacPaint...

Aah, the good old days. But as you say, we move on.

Regards,

Ian.

Someone could put together a bunch of Applescripts to create all kinds of actions to perform on a range of cells. Multiplying each by a given value is one possiblility. Here is a cobbled together script that will multiply all selected cells by a specified number. With one character change, it could divide, add, or subtract instead. With a better input dialog, the operation could be selectable.

set thevalue to text returned of (display dialog ¬

"Enter value to multiply by" default answer ¬

"" buttons {"Continue…"} ¬

default button 1)

tell application "Numbers" to tell the front document to tell active sheet to tell (first table whose class of selection range is range)

set twoNames to my decoupe(name of selection range, ":")

set {colNum1, rowNum1} to my decipher(item 1 of twoNames)

if item 2 of twoNames = item 1 of twoNames then

set {colNum2, rowNum2} to {colNum1, rowNum1}

else

set {colNum2, rowNum2} to my decipher(item 2 of twoNames)

end if

repeat with thecol from colNum1 to colNum2

repeat with therow from rowNum1 to rowNum2

set value of cell thecol in row therow to (value of cell thecol in row therow) * thevalue

end repeat

end repeat

end tell

on decoupe(t, d)

local l

set AppleScript'stext item delimiters to d

set l to text items of t

set AppleScript'stext item delimiters to ""

return l

end decoupe

on decipher(n)

local colNum, rowNum

if (character 2 of n) as text > "9" then

set colNum to ((ASCII number of (character 1 of n)) - 64) * 64 + (ASCII number of (character 2 of n)) - 64

set rowNum to (text 3 thru -1 of n) as integer

else

set colNum to ((ASCII number of (character 1 of n)) - 64)

set rowNum to (text 2 thru -1 of n) as integer

end if

return {colNum, rowNum}

end decipher

Someone could put together a bunch of Applescripts to create all kinds of actions to perform on a range of cells. Multiplying each by a given value is one possiblility.

I have a bunch of scripts that operate on currently selected cells, easily extending the Numbers 3 interface in useful ways. I find that approach simplifies life when extracting distinct values, applying conditional formatting, transposing, flagging duplicates, and for an operation like this. In Numbers 3 the scripting for operating on the current selection simplifies to something like this:

--select cells, run; converts positive numbers to negative; and vice versa

property myMultiplier : -1

tell application "Numbers" to tell the front document to tell active sheet to tell (first table whose class of selection range is range)

tell the selection range to repeat with i from 1 to count cells

tell cell i

try

set its value to (its value) * myMultiplier

end try

end tell

end repeat

end tell

--end of script

Easily modified to add a dialog.

SG

Your script is so miuch simpler. Is this simplicity something available only with Numbers 3? I can't get it to compile for Numbers '09. Yvan's scripts for Numbers always did a lot of tricks to get the selection range and operate on the individual cells within it (as in the script I posted which was mostly based on his code). If this simpler method is new to Numbers 3, it sure is a nice addition.

makfai wrote:

Really good but I am not sure it works on iPad

http://stackoverflow.com/questions/20286331/does-ios-7-support-applescript

True, but this is not the iOS Numbers forum. This thread is about the Mac version.

not sure it works on iPad

No AppleScript (yet) on the iPad. Automation there is limited to custom URL schemes (I don't think Numbers for iOS supports any) and also Python. It is also possible to access the clipboard (potentially a solution for the problem posed in this thread) and do a lot of other things with Pythonista.

SG

SG, the Scripting Guru.

Hi Ian,

Thanks for the kind moniker. I hadn't thought of that. Actually, I'm pretty much a beginner. The reason I stick as much as possible to smallish, simple scripts is that I don't really understand the big, complicated ones! I am fascinated by how much can be done, with relative ease, when scripting Numbers.

SG