Home

How to apply a division formula to display only the Integer (whole number) result?

%3CLINGO-SUB%20id%3D%22lingo-sub-823003%22%20slang%3D%22en-US%22%3EHow%20to%20apply%20a%20division%20formula%20to%20display%20only%20the%20Integer%20(whole%20number)%20result%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823003%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20apply%20a%20formula%20and%2For%20formatting%20to%20display%20only%20the%20integer%20result%20when%20both%20the%20input%20fields%20have%20decimal%20values.%3C%2FP%3E%3CP%3ESee%20my%20attached%20file%20-%3C%2FP%3E%3CP%3E1.%20Cell%20D28%20%3D%20the%20result%20of%20Cell%20D26%20%2F%20Cell%20K6%20-%20(9%2C85Kg%20%2F%200%2C5Kg)%20%3D%20only%2019%20and%20not%2019%2C7%20(as%20the%200%2C7%20represents%20only%2070%25%20of%20the%20following%20pack%20unit%20of%20500gr%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20Cell%20D28%20should%20only%20display%20the%20integer%20%3D%2019%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-823003%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823043%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20apply%20a%20division%20formula%20to%20display%20only%20the%20Integer%20(whole%20number)%20result%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823043%22%20slang%3D%22en-US%22%3E%3DINT(D26%2FK6)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823637%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20apply%20a%20division%20formula%20to%20display%20only%20the%20Integer%20(whole%20number)%20result%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F101635%22%20target%3D%22_blank%22%3E%40Johann%20Fourie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20so%20that%20you%20have%20choice%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20INT(%20weight%20%2F%20pack%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20QUOTIENT(%20weight%2C%20pack%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20FLOOR(%20weight%2C%20pack%20)%20%2F%20pack%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823685%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20apply%20a%20division%20formula%20to%20display%20only%20the%20Integer%20(whole%20number)%20result%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlus%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3D%20TRUNC(%20weight%20%2F%20pack%20)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824040%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20apply%20a%20division%20formula%20to%20display%20only%20the%20Integer%20(whole%20number)%20result%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrue.%26nbsp%3B%20Once%20one%20starts%20looking%2C%20there%20appears%20to%20be%20a%20bewildering%20number%20of%20options!%26nbsp%3B%20I%20could%20add%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20ROUNDDOWN(weight%2Fpack%2C%200)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20MROUND(%20weight%2C%20pack%20)%20%2F%20pack%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENot%20that%20I%20can%20see%20any%20difference%20between%20the%20%3CSTRONG%3EMROUND%3C%2FSTRONG%3Eand%20%3CSTRONG%3EFLOOR%3C%2FSTRONG%3Efunctions.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20OP%20can%20be%20interpreted%20not%20so%20much%20as%20'how%20can%20I%20perform%20the%20calculation%3F'%20as%20'which%2C%20of%20all%20the%20options%2C%20is%20the%20clearest%3F'.%3C%2FP%3E%3CP%3EI%20would%20favour%20one%20of%20these%20last%20two%20functions%20because%20the%20intent%20of%20each%20step%20of%20the%20calculation%20is%20clear%20but%20I%20could%20also%20go%20with%20%3CSTRONG%3EINT%3C%2FSTRONG%3Eon%20the%20grounds%20that%20it%20is%20the%20most%20basic%20function%20that%20will%20do%20the%20job.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824274%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20apply%20a%20division%20formula%20to%20display%20only%20the%20Integer%20(whole%20number)%20result%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPeter%2C%3C%2FP%3E%0A%3CP%3EIMHO%2C%20FLOOR%20works%20with%20negative%20numbers%20and%20MROUND%20not.%20But%20with%20both%20you%20may%20receive%20floating%20point%20error%20if%20second%20parameter%20is%20not%20integer.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Johann Fourie
Frequent Visitor

How do I apply a formula and/or formatting to display only the integer result when both the input fields have decimal values.

See my attached file -

1. Cell D28 = the result of Cell D26 / Cell K6 - (9,85Kg / 0,5Kg) = only 19 and not 19,7 (as the 0,7 represents only 70% of the following pack unit of 500gr

    Cell D28 should only display the integer = 19

 

See attached file

5 Replies

@Johann Fourie 

Just so that you have choice;

= INT( weight / pack )

= QUOTIENT( weight, pack )

= FLOOR( weight, pack ) / pack

@Sergei Baklan 

True.  Once one starts looking, there appears to be a bewildering number of options!  I could add

= ROUNDDOWN(weight/pack, 0)

= MROUND( weight, pack ) / pack

Not that I can see any difference between the MROUND and FLOOR functions. 

The OP can be interpreted not so much as 'how can I perform the calculation?' as 'which, of all the options, is the clearest?'.

I would favour one of these last two functions because the intent of each step of the calculation is clear but I could also go with INT on the grounds that it is the most basic function that will do the job.

@Peter Bartholomew 

Peter,

IMHO, FLOOR works with negative numbers and MROUND not. But with both you may receive floating point error if second parameter is not integer.

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