Bracketed negative numbers - How to line up with positive numbers

Question

Level 1

18 points

Bracketed negative numbers - How to line up with positive numbers

I regularly format negative numbers to display with brackets around them. Typically in a column of figures containing both positive and negative numbers.

I'm new to Apple Numbers. While I have found out how to display negative numbers with brackets, I have been unable to coerce their positive valued companions into lining up with them. Alignment at the decimal point is required.

I am using Apple Numbers v 3.5.3.

Can anybody point me to the solution ?

Excel/Open Office (and other spreadsheets?) do allow formatting instructions to achieve this.

The attached screen-shot is, I hope, self explanatory.

iMac, OS X Yosemite (10.10.5), Apple Numbers v 3.5.3

Posted on Oct 13, 2015 3:06 PM

Reply

Answer 1

SGIII

Level 8

35,288 points

Oct 13, 2015 4:34 PM in response to mw9

You can create a Custom Format in the Format > Cell panel. I named mine Accounting.

Add rules like this:

The result:

Note: you get the .00 by checking Show Trailing Zeros:

SG

Reply

Answer 2

mw9 Author

Level 1

18 points

Oct 13, 2015 5:29 PM in response to SGIII

Thank you very much, SG, for posting these detailed instructions.

I have replicated the steps which you have explained, but unfortunately my numbers just don't align like yours do.

Here is the result:

For avoidance of doubt, here is my "Accounting" custom format set up. I cannot see any difference between it and the version you showed above. It seems to be taking no notice of that trailing space. I've tried 'Narrow', 'Normal', and 'Wide' space types to no avail. The 'Flexible' space type just throws the positive numbers over to the left. And the 'Zero as hyphen' is also different, no amount of trailing spaces on it will shift it. Have I missed something ?

Reply

Answer 3

SGIII

Level 8

35,288 points

Oct 13, 2015 5:29 PM in response to mw9

In the Text panel make sure you have the cells right-aligned:

Also you may need to type a space at the beginning of Rule 2:

SG

Reply

Answer 4

mw9 Author

Level 1

18 points

Oct 13, 2015 5:25 PM in response to SGIII

That's fixed it ! Thank you.

The alignment was set to the default 'A' - Text to the left & Numbers to the right. It's very curious that there should be a difference in the result, but there is.

Many thanks indeed.

Reply

Answer 5

SGIII

Level 8

35,288 points

Oct 13, 2015 5:30 PM in response to mw9

Glad you prompted me into looking into Custom Formats more. They can do many of the things they do in Excel, but the approach is different and it sure can be a little tricky to get them to do the right thing. Anyway now we've got our Accounting format. Thanks for the green tick!

SG

Reply

Answer 6

mw9 Author

Level 1

18 points

Oct 13, 2015 5:50 PM in response to SGIII

You're more than welcome to the tick ! I'd give you two if I could, but you'll have to settle for a few "Helped me" stars. I've not seen the question answered anywhere else on t'internet.

I think I prefer the visibility of the Excel/OO way, simply putting spaces in the format strings are easily missed. And the text alignment thing is a bit of killer if you're new to the App.

I checked the decimal point alignments at 400%, they're spot on in Helvetica and Baskerville with a 'Normal' trailing space. Times New Roman is a milli-smidge out, but probably not visibly so when it matters. So the font metrics probably play a part. Which they shouldn't, really, in a perfect world.

Reply