Forum Discussion
Creating Formulas but not responding as expected.. Due to format of cell?
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.