Home

round to the nearest number

%3CLINGO-SUB%20id%3D%22lingo-sub-305963%22%20slang%3D%22en-US%22%3Eround%20to%20the%20nearest%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-305963%22%20slang%3D%22en-US%22%3E%3CP%3EHELLO%20%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20round%20the%20number%20in%20cell%20D6%20to%20the%20nearest%20number%20in%20raw%20(2)%20in%20the%20table%20(I%3AT%2C2%3A12).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecould%20you%20help%20me%20please%2C%3C%2FP%3E%3CP%3Ethank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-305963%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-305992%22%20slang%3D%22en-US%22%3ERe%3A%20round%20to%20the%20nearest%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-305992%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24J%242%3A%24T%242%2C0%2CMATCH(MIN(INDEX(ABS(%24J%242%3A%24T%242-D6)%2C0))%2CINDEX(ABS(%24J%242%3A%24T%242-D6)%2C0)%2C0))%3C%2FPRE%3E%0A%3CP%3Eif%20don't%20take%20into%20account%20possible%20float%20point%20calculation%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-305967%22%20slang%3D%22en-US%22%3ERe%3A%20round%20to%20the%20nearest%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-305967%22%20slang%3D%22en-US%22%3Ethe%20answer%20should%20be%202%20not%20.15%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-305966%22%20slang%3D%22en-US%22%3ERe%3A%20round%20to%20the%20nearest%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-305966%22%20slang%3D%22en-US%22%3Eit%20round%20to%20(%20.15%20)%20which%20isn't%20in%20raw%20(2)%20in%20the%20table%20(I%3AT%2C2%3A12).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-305965%22%20slang%3D%22en-US%22%3ERe%3A%20round%20to%20the%20nearest%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-305965%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EClassic%20INDEX()%20with%20double%20MATCH().%3C%2FP%3E%3CPRE%3E%3DINDEX(%24J%244%3A%24T%2413%2CMATCH(E4%2C%24I%244%3A%24I%2413%2C1)%2CMATCH(D4%2C%24J%242%3A%24T%242%2C1))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Abdullah_Khalid
New Contributor

HELLO ,

I want to round the number in cell D6 to the nearest number in raw (2) in the table (I:T,2:12).

 

could you help me please,

thank you.

4 Replies

Hi

 

Classic INDEX() with double MATCH().

=INDEX($J$4:$T$13,MATCH(E4,$I$4:$I$13,1),MATCH(D4,$J$2:$T$2,1))

 

it round to ( .15 ) which isn't in raw (2) in the table (I:T,2:12).
the answer should be 2 not .15

That could be

=INDEX($J$2:$T$2,0,MATCH(MIN(INDEX(ABS($J$2:$T$2-D6),0)),INDEX(ABS($J$2:$T$2-D6),0),0))

if don't take into account possible float point calculation error.

 

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
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
13 Replies