Numbers is automatically formatting my cells

Hi SG,

Thanks for your comment in this User Tip (written for Numbers 4.3):

Numbers 4.3 new Currency formats/functions

For now, I shall remain with Numbers 4.3 as the "anchor" until I am convinced that Apple has provided specific help on these new Financial functions. It behoves Apple to come clean and explain how these Financial functions really work.

• I am not a financial advisor.
• I know nothing about the Stock Market.
• I shall not provide further advice on Numbers Financial functions here in Apple Support Communities.

I am waiting for Apple to convince me (with an easy-to-understand Help document) that Numbers 4.3.1 will protect investors.

Regards,

Ian.

jamiefree wrote:

Whats happening is that I have a cell that is a currency and I am dividing it by a number to get a percentage. It's giving me the percentage as currency but won't let me format it back to percentage.

If you divide $100 by, say, the number 500, wouldn't you expect $0.20, not a percentage? That's what Numbers gives you.

If you divide $100 by $500 then Numbers knows you might be looking for a percentage and allows you to format the cell as a Percentage, Number, or Currency.

So if you want Percentage you can just format the divisor as Currency.

SG

Numbers now has a CURRENCYCONVERT function:

CURRENCYCONVERT(value, currency-code)

value: The value to be converted to a new currency. If value is not a currency value, the default currency (for your language and region) is used.

currency-code: The currency code of the target currency for the conversion.

This is much like the STOCK function introduced in previous versions of Numbers because it pulls the conversion rate directly from YAHOO from the previous day and updates each day to reflect the exchange rate changes. There should be no reason to manually enter exchange rates anymore 🙂

@SG III / Yellowbox

Here’s my situation:

I have a Numbers document that I am very proud of; it represents my startup in our investment seeking efforts. As with the software we produce, I am caring about the details and the experience of my end user.

In this Numbers document, we are showing a five year proforma. In some instances, we wish to show a salary, then the number of people that will be needed in this role.

For our potential investors, we don’t want them to stare at a speadsheet full of $ signs in front of every value in a (rather large) data set. We do want them to see $ signs in areas that provide totals, and in an area the defines a rate, such as the aforementioned salary.

With Numbers 4.3’s autoformatting, this ability is simply not possible. If you multiply by a currency value, the end result is receiving a currency formatting, with no option in the formatting area to display a currency value without a currency symbol.

Additionally, if you reference a cell that has been formatted as a currency, such as in another table’s `Total` row, then you will have this currency enforced on the referencing cell.

So with the update to Numbers 4.3, we suddenly have some tables (which have cells that referenced other table’s `Total`s values) showing currencies, alongside cell values that do not have the $ signs, leading to an ugly presentation.

This is, with all respect, an oversight.

I have attempted to utilize `VALUE`, and unfortunately with a multiplication operation, this seems to not be a workaround.

Here is a view that adequately shows a table where it would **not** be nice to have $ signs littered everywhere, because it’s distracting and unneeded, but you do want them in the `Total`s lines:

This hurts because there is a simple solution: provide an option in the formatting of currencies to not display a currency symbol. Instead, I need to decide if my presentation will have $ signs everywhere, or nowhere, because currently there is no in-between that I am aware of. Can you provide any help here?

jordoapp wrote:

there is a simple solution: provide an option in the formatting of currencies to not display a currency symbol. Instead, I need to decide if my presentation will have $ signs everywhere, or nowhere, because currently there is no in-between that I am aware of. Can you provide any help here?

Yes, and that easy solution already exists. Where you want to display $ or whatever currency symbol, just use a Custom Format instead of the built-in Currency format. In the Data Format dropdown list choose 'Create Custom Format...' and do something like this.

In your case you may not want the decimal places. In that case just delete the .00 token from the Custom Format: box.

After you create this Custom Format it will appear in the dropdown and can easily be applied just like any other format.

Then, for the cells where you don't want $ signs, just use Number format.

You can do all kinds of things with Custom Format to dress up your documents.

SG

SG III please let me start by stating that you are the man. Love the suggestion of using custom formats, however I don't think they solve this issue of Numbers 4.3 autoformatting. Please allow me to explain:

My custom format:

Prior to formatting a footer row:

After formatting footer row with custom format, the autoformatting of Numbers 4.3 still occurs:

Unfortunately those autoformatted cells cannot be changed; when attempting a change, they automatically revert back to autoformatted layout.

SG III, let's keep trying, I do appreciate your responses. I'll file a radar with the above information and list here if others would be so kind as to provide a bump.

"This is simply ridiculous. Now I have to add a VALUE formula to make a simple currency conversion otherwise Numbers thinks it is more clever than me. Utterly ridiculous."

Or you could simply format the starting value as a number, multiply it by the conversion factor (also a number), and format the result as a number. Use the column headers to label the currency units that the numbers represent.

Regards,

Barry

Hi everyone,

An enjoyable and educational discussion!

Following on from Barry's insight where the units are implied by the column headers:

No need for any format other than Number.

Keep up the lively debate!

Regards,

Ian.

Hi SG,

Or use Special Format (made to look like Currency) divided by Number.

Or, continuing the bananas and monkeys paradigm, we can use a Number in a Custom Format 👿.

Keep smiling, my friend. We will get this worked out.

Regards,

Ian.

LMHMD wrote:

ralphmcgraw, here's an example: Create a cell (say Q3) that is an "annualized percentage increase" - calculated from 3 other cells: IF(P3≠0,365÷(DATEDIF(C3,WORKDAY(J3,3),"D"))×P3,"")

1. P3 is a dollar amount

2. C3 is a a start date

3. J3 is an end date

If P3 is formatted as currency, one cannot format Q3 as a percentage. If, however, P3 is formatted as a percentage, Q3 can be formatted as a percentage or as currency.

Here are the results of that formula, placed in a Numbers 5.6.2 table, where the result in Q3 can be formatted as a percentage:

Example 1: Q3 format is "Automatic". Numbers chooses the Currency format used in P3:

Example 2: Same data and formula. Q3 formatted as "Percentage" with decimal places set to 0.

Is 197,297% the Annual Percentage Increase you would expect from these figures?

If so, what does the number in P3 represent? What is the base value from which the 'increase' is measured? Or, in other words, "this figure is the 'Annual Percntage Increase' from what?"

The number in Q5 is the number of days in the period from January 1 to the third work day after July 1 in 2017, using the default 'non-working' days defined as part of the function. DATEDIF returns a number, so this value is a number, not a duration.

That result is calculated by this part of your formula:

DATEDIF(C3,WORKDAY(J3,3),"D"))

The number in Q7 is the result of dividing 365 by the result in Q5. As the number of working days (as determine by the WORKDAYS() function) will be less that the number of days in any portion of a year, up to the third working day before the end of a full year, this result will almost always be greater than 1. This result is calculated by this part of your formula:

365÷(DATEDIF(C3,WORKDAY(J3,3),"D"))

Clariification, Please.

Regards,

Barry

Hi SG,

I am not worried about updating to Numbers 4.3.1 because I do not use Numbers Financial functions. True, we are not providing financial advice, merely advice on how the Financial functions are intended to work.

My concern is that App Store is not giving clear advice. App Store failed to warn users that Numbers 4.3 would remove the ability to Cell > Data Format > "my currency" hence our recent troubles.

App Store tells me that Numbers 4.3.1 "contains stability and performance improvements" without a "More" button.

Yeah, right. No mention of bug fixes for Financial functions. What should a user expect?

The Formulas and Function Help may well be useful, but until Apple comes clean and explains how these Financial functions are intended to be used, I shall stay away from answering questions that involve those functions.

I am happy to help with other questions in Numbers 4.3.1

Good to hear that Numbers 4.3.1 does not have any salties 🙂.

Regards,

Ian.

Hi SG,

Sorry for my rant. It was not aimed at you, but aimed at App Store that fails to inform users of all changes in an update.

I updated to Numbers 4.3.1

I am happy to see this in Numbers 4.3.1

The "Silly exchange rate" is 2, simply as a test value to multiply the source currency (Column B).

Good to see that we can now format the result as either Euros (Column D) or our "home" currency (Column E).

Thank you for your positive approach.

Regards,

Ian.

Yes the release notes are certainly a little, shall we say, "terse." Firing up an update is a little like opening a surprise package; probably something good in there, but, well, one never really knows without diving in.

In case you haven't seen it the included 'My Stocks' template (at File > New) in the menu provides good examples on how to use STOCK and STOCKH. Clever how it hides the weekends and other non-trading days with a filter.

The CURRENCY functions are similar. All very convenient. Hope they continue to work!

SG

Hi aro,

It's not SGIII's product. Nor is it the product belonging to any of the people posting here. This is a user-to-user support community in which Apple's involvement is minimal.

The people responding to questions can tell you what the software does, how to use the software to get the result you want, bearing in mind what the software does do. They have no control over whether the software will do what you think it should do in the way you want it done.

The software is what it is. If you want to advise Apple what it should be, then talk to Apple. Launch Numbers. Go to the Numbers menu. Choose Provide Numbers Feedback. Then make your request, or file a bug report.

While you're waiting for action, reread the posts in this discussion, take note of the solutions suggested, and pick one that will work for you until Apple fixes this issue.

There's a summary of suggestions in this User Tip: Numbers 4.3 new Currency formats/functions

REgards,

Barry

Hi,

I saw this too on a spreadsheet I'm working on. I think you can use the function VALUE in your formula

Try this:

In cell B2 enter $123.00. Enter 2 in cell B3, then in cell B4 use =VALUE(B2) / B3

You can then format B4 as whatever you want

Hope that helps!