Numbers is automatically formatting my cells

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.

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.

ralphmcgraw wrote:

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.

See my post upthread. Currency divided by Number gives Currency, which currently can't be reformatted as Percentage.

If you need Percentage then you can use Number divided by Number. Or use Special Format (made to look like Currency) divided by Number.

SG

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.

This is undoubtedly a bug, and I'm sure how helpful it is to suggest updating potentially 1000s of spreadhseets. However, I have found that just putting ROUND(<currency column>,2) in my formula does allow the derived column to avoid the autoformat to currency. The column will then allow the format to be switched to number.

I understand you have to support your product but...

This discussion is not about the correct assumption Numbers makes. It's about my ability to format cells in anyway i want. If i decide to divide $100 between 10 apples and Numbers assumes i'm expecting a decimal by default it's understandable, it's great actually.... now, because i'm not expecting a decimal, but a percentage, i should be able to correct it.

My product?! Before posting I’m sure you did do at least a couple minutes of homework and therefore know that we are fellow users here trying to help others with Apple’s products as they are currently designed. Right? 😉

If you’ve got feedback for Apple on how they could do things to better meet your needs then you may find the most effective way is to go to Numbers > Provide Numbers Feedback in your menu.

SG

Then i have no explanation for you coming here justifing what it seems to be a bug, and telling the user this is a better way to go, despite his frustration... i’m sure you did at least a little homework and checked past versions where it doesn’t happen ;).

Let’s hope it will be fixed.

"Let’s hope it will be fixed."

Indeed.

In the meantime, reread the discussion to determine what solutions are possible while waiting for that to happen.

My post, and I expect, SG's are not intended to "justify" a bug, but to remind you that here you are talking with user of the software, not developers of the software. We can offer you solutions that are applicable to the software as it currently exists. We can't change the software to meet your needs (or ours, for that matter).

Regards,

Barry

Barry wrote:

"Let’s hope it will be fixed."

Indeed.

In the meantime, reread the discussion to determine what solutions are possible while waiting for that to happen.

My post, and I expect, SG's are not intended to "justify" a bug, but to remind you that here you are talking with user of the software, not developers of the software.

Well said, Barry.

SG

I have to agree that this automatic enforcement is obnoxious.

An example:

A1 uses custom format #,### ft2.

A2 is formatted as a currency.

A3 = A1 / A2

Cell A3 should use the custom format $#,###.00 psf but it will not comply. Even if I use the Value() function, A3 is forced into simple currency format.

Such an unecessary imposition. Just gross.

Hi Palmer,

It is what it is, and the users here have no power to change it.

The working solution is to ignore the currency format in A2 and leave it as a bare number. An alternate is to use a custom format to show that it's currency.

The long-term solution starts with using the Provide Numbers Feedback item in the Numbers menu to 'suggest' that this be fixed.

Regards,

Barry

I see there is an update to Numbers to 4.3.1, as of Nov. 09. Can anyone say if this ridiculous bug has been corrected from 4.3?

I'm on the road without my TM backups, so I don't want to download to test if I can't revert to 4.2 easily.

Hi Ian,

I wouldn't worry about installing the new version 4.3.1. It appears to have squashed some bugs.

The currency and other financial functions (which Apple has covered in Formulas and Functions Help) are simply a convenient way to retrieve data from Yahoo! Finance, a widely used site. Explaining to users how the functions can do that is not providing financial advice!

Yahoo! Finance, of course, is not infallible as a data provider. Nor, for that matter, is Bloomberg or anyone else. So one should use the convenience of Numbers with the same caution that one would use in going directly to data providers. Mistakes do creep into the data.

So come on in. The water is fine! (At least I haven't spotted any salties yet.🙂)

SG