Numbers is automatically formatting my cells

Did you read my post? It is not about %s, but about currency conversion (therefore same theme of "automatic formatting" forced by Numbers).

If I create a cell and assign it to a currency, let's say $. I have a cell next to it with no unit, that is an exchange rate. The multiplication of the two cells can now (and not until previous versions) only be a value in $ unless I VALUE() the original figure in $. So, I have to add a useless formula to convert $ to €.

In any case, Numbers should not force a specific formatting on me. If it thinks it is smarter than me, just do like Excel and give a recommendation that something looks odd—but leave it up to the user to play with it or not.

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 🙂

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

This is ridiculous.

What is the benefit of this change?

The currency converter does do historical exchange rates only current, nor does it allow for negotiated rates.

Instead I have now transferred all my spreadsheets to excel as it would take too long to go through literally thousands of fields and make the necessary changes.

Nice move Apple.

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.

Ok then. Let me provide you my costumer files which I have seen useing for nearly a decade and you change the format on nearly 13,000 cells.

Yes, there is a work around, for a forced new feature that has no beifit.

Hi genocchio, SG III helps a lot of people on these forums, no need to direct frustration his way.

I do believe we can expect a fix to this in an update, as long as the position is properly represented via the established feedback channels. It's a bug, not a feature, plain and simple.

I appreciate your understanding that we need not direct our frustration at people unaffiliated simply providing help.

"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."

Hi Jamie,

After reading through the whole thread, I'm still puzzled by your opening statement.

Can you explain, using a specific example?

Suppose your currency value is $20.00

And the number you divide it by is 4.

What result do you expect?

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.

I tried this and it didn't work. I also have a sum that has for several days been subtracted from a total and not Numbers will no longer do it. This new up date has completely screwed up years of spread sheets. Any way I can go back to the old version?

ralphmcgraw wrote:

I tried this and it didn't work.

Before I post suggestions I test them on my machine.

So can you kindly advise what the "this" is that "didn't work" for you, and describe the problem you see specifically enough so someone can try to help?

SG

I HAVE SPEAD SHEETS ON INVESTMENTS GOING BACK YEARS . FOR GAINS AND LOSSES I TAKE THE VALUE OF TODAY'S INVESTMENTS AND DIVIDE IT BY YESTERDAY'S VALUE. INSTEAD OF A PERCENTAGE I NOW GET $1 AND WON'T LET ME CHANGE THATVALUE TO A PERCENTAGE.

PROBLEM 2. I HAVE BEEN KEEPING THE VALUES OF STOCK SALES ON MY SPREAD SHEE FOR THE REST OF THE MONTH IN CASE I NEED TO LOOKE IT UP LATER FOR IRS PURPOSES. I SUBTRACT THIS FROM TO FINAL SUM. THE NEW UPDATE WONT LET ME DO THIS FOR SOME OBSCURE REASON.

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.

Yellowbox wrote:

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 👿

Hi Ian,

Good one! Where did you find those monkeys willing to share partial bananas!

It is impressive what can be done with Custom Format.

So far Numbers 4.3 working well here.

SG

"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."

Thanks so much for the above suggestion, Barry! I have a rather complex spreadsheet with currency conversions to-and-fro between EUR, GBP CHF and USD all over the place, and the update to Numbers 4.3 had messed everything up. Instead of faffing about with new formulas etc. I have adopted your above suggestion and it works great — keeps things nice and simple (important to me on a large and complex spreadsheet) and even looks better! 🙂 I really don´t understand why Apple in their infinite wisdom made this change to 4.3 but there we are! I think they´re more busy with/interested in devices with very small screens at the moment! 👿

Thanks again! Cheers, Ian.