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
Highlighted
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
Highlighted
Highlighted

@Johann Fourie 

Just so that you have choice;

= INT( weight / pack )

= QUOTIENT( weight, pack )

= FLOOR( weight, pack ) / pack

Highlighted
Highlighted

@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.

Highlighted

@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