Home

Round Down to the nearest 5

%3CLINGO-SUB%20id%3D%22lingo-sub-1201991%22%20slang%3D%22en-US%22%3ERound%20Down%20to%20the%20nearest%205%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1201991%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20my%20problem%20is%2011600%2F3%20%3D%203867%2C%20but%20I%20need%20it%20to%20round%20down%20to%20the%20nearest%205.%20How%20can%20I%20set%20my%20formula%20to%20do%20this%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1201991%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1202149%22%20slang%3D%22en-US%22%3ERe%3A%20Round%20Down%20to%20the%20nearest%205%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1202149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571014%22%20target%3D%22_blank%22%3E%40jshea23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20could%20be%20(assume%20A1%20contains%20the%20number%20you%20want%20to%20round)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINT(A1%2F5)*5%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20will%20always%20round%20to%20the%20%3CSTRONG%3Eprevious%3C%2FSTRONG%3E%20multiple%20of%205%20(i.e.%20rounding%20down).%20If%20your%20intention%20is%20to%20round%20to%20the%20%3CSTRONG%3Enearest%3C%2FSTRONG%3E%20multiple%20of%205%20it%20could%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMROUND(A1%2C5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

So my problem is 11600/3 = 3867, but I need it to round down to the nearest 5. How can I set my formula to do this??

2 Replies
Highlighted

@jshea23 

This could be (assume A1 contains the number you want to round):

=INT(A1/5)*5

This will always round to the previous multiple of 5 (i.e. rounding down). If your intention is to round to the nearest multiple of 5 it could be:

=MROUND(A1,5)

 

Highlighted

@jshea23 

Just in case, to cover negative numbers

=MROUND(A1,SIGN(A1)*5)

or

=ROUND(A1/5,0)*5

 

Related Conversations