Numbers is automatically formatting my cells

Thank you to s14kev for this helpful tip about using the VALUE function.

But like genocchio and jamiefree, I also spent (wasted) an inordinate amount of time going through spreadsheets and typing "VALUE" all over the place. Time that I will never get back. Thanks Apple! What would have possessed anyone to make this useless change in ver 4.3 of numbers; why would anyone in his right mind think that removing the ability to set the data format that you want was a good idea? My guess is that the people who made this design change know everything about coding, and nothing about actually using a spreadsheet in the real world.

acfda001 wrote:

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.

Did you see my post above? You do not need to add a VALUE function, nor do I think you should add VALUE (unless you are having to clean up something that has been imported as strings with the character $ in them, etc.).

If you want a result as a percentage then just make sure both numbers are formatted as Currency (or Number) . It takes a few seconds to make sure the data formats are consistent.

If you are making a "simple currency conversion" (i.e. you don't want a percentage) then Numbers gives you what you want directly, as shown in the post above.

SG

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.

My point is the following. There is a distinction to be made between units and formats. When I type say, 0.25 in a cell, I can chose the display format to be a number (0.25), a fraction (1/4) or a scientific representation (2.5e-01). What you as a user mean by 0.25 is something else. Does it represent a speed? a weight? heat flow? a probability? In other words Numbers does not care about the units associated with that value.

Except, so it seems, and only in version 4.3, if you choose the format to be currency. In that case, Numbers seems to say, hey, if you divide a dollar by 2, you results should be expressed in dollars. In other words, Numbers is treating the format as a unit.

So, consider the following scenario:

• one cell contains an amount of money to buy, lets say shares, formatted as a currency.

• another cell, which represents the unit cost of each share expressed as a currency.

When calculating how many share your money can buy, version 4.3 of Numbers does both the math and the unit conversion for you and let you format the resulting number as you so which (try it).

Now, you might argue (and I agree with you) that the unit cost is not a currency, i.e. that the units are not $ but $/share. So, since Numbers does not have a way to select "display this as a quantity whose units are $/share", what should I chose amongst Number's display possibilities? Display as currency or simply display as number?

Version, 4.3 makes a strong statement about this while previous version did not.

My preference? don't confuse formats and units and don't try to be too clever.

Just sayin'

Hope this helps.

jordoapp has hit the nail on the head; that's the issue. The accepted best practice for a column of numbers is for the top number and the bottom number (usually a total) in the column to be shown as Currency (e.g., "$"). Conventionally, all the numbers in between are shown as Number format; that is, without a currency symbol. The problem is that ver 4.3 of Numbers has forced the format of those mid-column numbers to be Currency when they are calculated from another cell which is formatted as currency.

No, SGIII's suggestion of using Custom Format does not solve this problem, because Numbers ver 4.3 will not allow the cell in question to be any format other than Currency, including Custom. You can demonstrate this for yourself:

A1 - format Currency

A2 - format Number

A3 = A1*A2

The format of cell A3 will be locked as Currency, and cannot be changed.

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.

MainTrimmer wrote:

Numbers ver 4.3 will not allow the cell in question to be any format other than Currency, including Custom. You can demonstrate this for yourself:

A1 - format Currency

A2 - format Number

A3 = A1*A2

The format of cell A3 will be locked as Currency, and cannot be changed.

True, but my suggestion was:

A1 - format Special Format (which is made to look like currency as explained above.

A2 - format Number

A3 - A1*A2

A3 can be formatted as you wish.

SG

Hi genocchio,

We are your fellow users here. If you wish to communicate with Apple you can go to Numbers > Provide Numbers Feedback in your menu.

I'm scratching my head somewhat about the perceived problem, especially when within a few seconds I can do just almost anything I want via Custom Format if I don't want the behavior of the built-in formats (which I generally find to be logical and useful).

SG

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

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

Same for me, jamiefree. The only "fix" I could make was to change the format of one of the cells used in the formula to calculate my mis-behaving cell to a generic number format. This only happened since the last Numbers update, and I now suspect the update introduced an attempted refinement gone bad!