SOLVED
Home

Unable to set some cells to two decimal places

%3CLINGO-SUB%20id%3D%22lingo-sub-344267%22%20slang%3D%22en-US%22%3EUnable%20to%20set%20some%20cells%20to%20two%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-344267%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attachment%20shows%20an%20account%20book%20I'm%20working%20on.%20The%20problem%20is%20that%20I%20can't%20get%20some%20cells%20to%20show%20to%20only%20two%20decimal%20places%2C%20while%20others%20will.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20S%20is%20an%20amount%20ex-tax%2C%20column%20T%20is%2020%25%20tax%20on%20the%20column%20S%20amount%20%5Busing%20the%20formula%20%3D(S323)*20%25%5D%2C%20and%20column%20U%20is%20the%20total%20of%20columns%20S%20and%20T.%20Notice%20that%20%C2%A3200.33%20*%2020%25%20%3D%20%C2%A340.066p%20but%20cell%20T%20correctly%20shows%20it%20as%20%C2%A340.07p%20because%20I%20have%20it%20set%20to%20currency%2C%20showing%20to%20two%20decimal%20places%3B%20so%20column%20U%20correctly%20shows%20%C2%A3240.40p%20(%C2%A3200.33%20%2B%20%C2%A340.07p).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20notice%20cell%20Z326%2C%20which%20is%20simply%20cell%20Z325%20minus%20cell%20U324%20(showing%20in%20the%20formula%20bar%20as%20%3DIMSUB(U324%2CZ325).%20Where%20cell%20U324%20correctly%20shows%20%C2%A3240.40p%2C%20cell%20Z326%20should%20also%20show%20%C2%A3240.40p%2C%20but%20it%20doesn't%2C%20it%20shows%20%C2%A3240.396%20instead%2C%20which%20is%20%C2%A3200.33p%20plus%20%C2%A340.066p.%20Therefore%2C%20for%20some%20reason%2C%20cell%20U324%20shows%20to%20two%20decimal%20places%20but%20cell%20Z326%20won't.%20Notice%20also%20cell%20N327%20(which%20is%20cell%20T324%20minus%20cell%20M324)%20also%20shows%20the%20lack%20of%20two%20decimal%20places.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20cells%20N327%20and%20Z326%2C%20I%20have%20them%20both%20successfully%20set%20to%20currency.%20However%2C%20neither%20cells%20(along%20with%20many%20other%20cells)%20will%20respond%20to%20selecting%20two%20decimal%20places%2C%20either%20via%20the%20'home%20tab%20%26gt%3B%20number%20group%20%26gt%3B%20format%20cells%20%2F%20currency%2C%20and%20selecting%202%20decimal%20places'%2C%20or%20via%20the%20'increase%2Fdecreas'%20decimal%20buttons%20on%20the%20'home%20tab%2C%20number%20group'%2C%20buttons.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20how%20can%20I%20get%20them%20to%20show%20to%20two%20decimal%20places%20only%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-344267%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352181%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20set%20some%20cells%20to%20two%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352181%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20done%20the%20trick!%20Thanks%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ER%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352096%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20set%20some%20cells%20to%20two%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352096%22%20slang%3D%22en-US%22%3E%3CP%3EOops%2C%20sorry%2C%20I%20missed%20first%20time%20you%20use%20IMSUB.%20That%20function%20returns%20the%20difference%20of%20two%20complex%20numbers%20%3CSTRONG%3Eas%20the%20text%3C%2FSTRONG%3E%20(%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fimsub-function-2e404b4d-4935-4e85-9f52-cb08b9a45054%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fimsub-function-2e404b4d-4935-4e85-9f52-cb08b9a45054%3C%2FA%3E).%20You%20don't%20need%20it%20all.%20Instead%20of%20%3DIMSUB(U324%2C%20Z325)%20use%20simply%3C%2FP%3E%0A%3CPRE%3E%3DU324-Z325%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352076%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20set%20some%20cells%20to%20two%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352076%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20reply%20but%20no%20joy%20I'm%20afraid.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20highlighted%20the%20cell%20(Z326)%20and%20hit%20Fn%2BF2%2C%20which%20made%20the%20cells%20formula%20%5B%3DIMSUB(U324%2CZ325)%5D%20appear%20in%20the%20cell.%26nbsp%3B%26nbsp%3BI%20then%20hit%20Enter%2C%20which%20just%20made%20the%20green%20selection%20border%20around%20the%20cell%20move%20one%20cell%20down%2C%20so%20the%20cell%20below%20Z326%20became%20selected.%20This%20had%20no%20effect%20on%20the%20number%20in%20cell%20Z326%2C%20still%20showing%20to%20three%20decimal%20places.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I've%20noticed%2C%20however%2C%20is%20that%20this%20problem%20is%20only%20occurring%20in%20cells%20that%20have%20%3DIMSUB%20in%20the%20formula%3B%20all%20the%20others%20without%20IMSUB%20work%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ER%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352025%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20set%20some%20cells%20to%20two%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352025%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20happen%20if%20your%20cell%20initially%20was%20in%20text%20format%2C%20applying%20another%20format%20won't%20convert%20text%20to%20number%20automatically.%20Try%20to%20stay%20on%20such%20cell%2C%20F2%20and%20Enter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352024%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20set%20some%20cells%20to%20two%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352024%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20shown%20in%20screenshot%201%2C%20one%20of%20the%20target%20cells%20(Z326)%20is%20selected%2C%20and%20you%20can%20see%20that%20it%20is%20set%20to%20currency%2C%20to%20two%20decimal%20places.%20However%2C%20for%20some%20reason%2C%20this%2C%20and%20the%20other%20cells%20that%20are%20not%20cooperating%20(such%20as%20N327)%2C%20will%20not%20act%20as%20though%20they%20are%20currency%20cells%20to%20two%20decimal%20places%3B%20as%20shown%20in%20screenshot%202%2C%20they%20seem%20to%20act%20as%20though%20they%20are%20set%20to%20'general'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20how%20to%20rectify%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ER%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-351947%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20set%20some%20cells%20to%20two%20decimal%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-351947%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBased%20on%20your%20screenshort%20currency%20format%20is%20not%20set%20for%20these%20cells.%20Try%20to%20stay%20on%20any%20of%20these%20cells%20in%20question%20and%20press%20Ctrl%2B1%2C%20formatting%20window%20shall%20show%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20399px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83349i0E00FF3E6B78B34D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3Bif%20everything%20is%20correct%20with%20formatting.%20If%20not%2C%20change%20the%20format%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Reboot
Occasional Contributor

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.

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

image.png

 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

Thanks for the reply but no joy I'm afraid.

 

I highlighted the cell (Z326) and hit Fn+F2, which made the cells formula [=IMSUB(U324,Z325)] appear in the cell.  I then hit Enter, which just made the green selection border around the cell move one cell down, so the cell below Z326 became selected. This had no effect on the number in cell Z326, still showing to three decimal places.

 

What I've noticed, however, is that this problem is only occurring in cells that have =IMSUB in the formula; all the others without IMSUB work fine.

 

Any ideas?

 

R

Solution

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

 

That's done the trick! Thanks for your help.

 

Cheers

 

R

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies