Forum Discussion
Unable to set some cells to two decimal places
Hi
The attachment shows an account book I'm working on. The problem is that I can't get some cells to show to only two decimal places, while others will.
Column S is an amount ex-tax, column T is 20% tax on the column S amount [using the formula =(S323)*20%], and column U is the total of columns S and T. Notice that £200.33 * 20% = £40.066p but cell T correctly shows it as £40.07p because I have it set to currency, showing to two decimal places; so column U correctly shows £240.40p (£200.33 + £40.07p).
However, notice cell Z326, which is simply cell Z325 minus cell U324 (showing in the formula bar as =IMSUB(U324,Z325). Where cell U324 correctly shows £240.40p, cell Z326 should also show £240.40p, but it doesn't, it shows £240.396 instead, which is £200.33p plus £40.066p. Therefore, for some reason, cell U324 shows to two decimal places but cell Z326 won't. Notice also cell N327 (which is cell T324 minus cell M324) also shows the lack of two decimal places.
With cells N327 and Z326, I have them both successfully set to currency. However, neither cells (along with many other cells) will respond to selecting two decimal places, either via the 'home tab > number group > format cells / currency, and selecting 2 decimal places', or via the 'increase/decreas' decimal buttons on the 'home tab, number group', buttons.
So, how can I get them to show to two decimal places only?
Thanks.
Oops, sorry, I missed first time you use IMSUB. That function returns the difference of two complex numbers as the text (https://support.office.com/en-us/article/imsub-function-2e404b4d-4935-4e85-9f52-cb08b9a45054). You don't need it all. Instead of =IMSUB(U324, Z325) use simply
=U324-Z325
6 Replies
Hi,
Based on your screenshort currency format is not set for these cells. Try to stay on any of these cells in question and press Ctrl+1, formatting window shall show
if everything is correct with formatting. If not, change the format here.
Hi
Thank you for your reply.
As shown in screenshot 1, one of the target cells (Z326) is selected, and you can see that it is set to currency, to two decimal places. However, for some reason, this, and the other cells that are not cooperating (such as N327), will not act as though they are currency cells to two decimal places; as shown in screenshot 2, they seem to act as though they are set to 'general'.
Any ideas how to rectify this?
Regards
R
Hi,
That could happen if your cell initially was in text format, applying another format won't convert text to number automatically. Try to stay on such cell, F2 and Enter
