Forum Discussion
Creating Formulas but not responding as expected.. Due to format of cell?
I have a large moderately complex workbook for forecasting / projecting a financial situation for next 30 years.
It references data on multiple sheets. I'm using Accounting format, but I have removed / shifted the decimal places so it shows Custom for the format.
It has been working fine, but yesterday when I attempted some changes, all of a sudden when I create a formula, even something as simple as =H36 or as simple IF statement, the cell remains empty or shows 0!
I just tried something in some unused cells, where I did a simple =E31, which contains the value 12%. Three of the four cells show 12% the other $ 0. The three that show 12% were probably General to start, but now show percentage. The one that didn't show 12%, was Custom, from me using it previously to test thigs.
I then formatted a blank cell Accounting and shifted decimals (sand it reacts the same way, and show $ 0, not 12%.
What is going on? LOL
E31 is manually entered 12%
I entered =E31 in each of these cells below. (It won't let me use a table here...) They started as General. The one that doesn't show 12% was preformatted Accounting but with decimals shifted, so it shows Custom. The last one I formatted as Accounting without removing / shifting the decimals and it pulled in the 12%... I'm lost. What's gone whacky with the Custom format after shifting the decimals? It has been working fine.
Thanks
12%
12%
$ 0
12%
12%
12%
2 Replies
Seems the issue is caused by the Custom number format you created when shifting decimals in Accounting format. Custom formats can override how Excel displays values, and if the format string does not include a placeholder for percentages, Excel will show $0 instead of the actual value. The formulas are working fine, it is the display format that masking the result.
First of all, this problem only recently started. Anytime I just wanted a cell to equal the contents of another cell or any formula I used, all that worked. I have had this sheet going for years, originally as Currency but changed a year ago to Accounting, because I wanted to move the $ to the left and wanted negative values parenthesized.
So, I just tried entering some numbers and a simple formula in unused cells, in Accounting format and formatted destination cells in Accounting, and they all worked, of course. I then removed the decimal places and it still worked as Custom for THOSE new cells, even with mixed formats.
Then I tried for example, in a couple of the new cells, I put =I36 (which is one of the cells that is not transferring in my previous scenario), and it did NOT work. So, it seems the problem may be with the source cell, which I have tried both Accounting and Currency and it doesn't work. I then changed I36 and the destination cell to Number format and I still get a zero. Very odd..
So... I just replaced the formula in I36 which was simple M23-J23+J34 or whatever, with a manually entered number and lo and behold it worked... even though both that I36 and it's =I36 destination are both Custom formatted. So there is something about that source cell formula that is weird. Maybe there is a circular reference issue somewhere. I'll keep looking.
