{"id":3932,"date":"2019-01-09T13:10:47","date_gmt":"2019-01-09T13:10:47","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=3932"},"modified":"2024-03-30T11:30:10","modified_gmt":"2024-03-30T11:30:10","slug":"round-to-nearest-value","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/","title":{"rendered":"Round to Nearest Value in Excel"},"content":{"rendered":"\n<div class=\"wp-block-file\"><a id=\"wp-block-file--media-ef2e24c7-b3df-4405-921d-6fe55b4eea25\" href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/01\/Round-to-Nearest-Value.xlsx\">Round-to-Nearest-Value<\/a><a href=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/01\/Round-to-Nearest-Value.xlsx\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-ef2e24c7-b3df-4405-921d-6fe55b4eea25\">Download File<\/a><\/div>\n\n\n\n<p>Sometimes, displaying a very precise number is not useful and you may want to round the value. But there may be cases when you want to round the number to the specified nearest value and not to the nearest integer.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>The rounding function works like this.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=ROUND(A2,1)<\/pre>\n\n\n\n<p>It rounds the value to the nearest float value with a precision of 1.<\/p>\n\n\n\n<p>This number <strong>12.3456<\/strong> will be displayed as <strong>12.3<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"527\" height=\"140\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png\" alt=\"\" class=\"wp-image-3934\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png 527w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example-300x80.png 300w\" sizes=\"(max-width: 527px) 100vw, 527px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Rounding the number to the nearest 10, 100, 1000<\/h2>\n\n\n\n<p>The simplest way to round numbers to the nearest 10, 100, or 1000, is to add a negative number to the second argument of the <a href=\"https:\/\/officetuts.net\/excel\/en\/functions\/round\">ROUND function<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>Nearest 10<\/td><td>=ROUND(A2,-1)<\/td><\/tr><tr><td>Nearest 100<\/td><td>=ROUND(A2,-2)<\/td><\/tr><tr><td>Nearest 1000<\/td><td>=ROUND(A2,-3)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Here, are the result of these formulas.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"446\" height=\"140\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-negative-argument.png\" alt=\"\" class=\"wp-image-3935\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-negative-argument.png 446w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-negative-argument-300x94.png 300w\" sizes=\"(max-width: 446px) 100vw, 446px\" \/><\/figure>\n\n\n\n<p>This works only for the powers of tens: 10, 100, 1000. If you want to do it for 5 or 50, you have to take a different approach.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Rounding the number to the nearest 5, 10, 50, 100<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>Nearest 5<\/td><td>=ROUND(A2\/5,0)*5<\/td><\/tr><tr><td>Nearest 10<\/td><td>=ROUND(A2\/10,0)*10<\/td><\/tr><tr><td>Nearest 50<\/td><td>=ROUND(A2\/50,0)*50<\/td><\/tr><tr><td>Nearest 100<\/td><td>=ROUND(A2\/100,0)*100<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"527\" height=\"140\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-different-values.png\" alt=\"\" class=\"wp-image-3936\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-different-values.png 527w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-different-values-300x80.png 300w\" sizes=\"(max-width: 527px) 100vw, 527px\" \/><\/figure>\n\n\n\n<p>You can use this simple formula to round a number to the nearest value.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Rounding number to the nearest value &#8211; MROUND<\/h2>\n\n\n\n<p>But there is another function you can use to achieve the same effect &#8211; <a href=\"https:\/\/officetuts.net\/excel\/en\/functions\/mround-function\">MROUND<\/a>. It gets two parameters: the number and the multiple.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>Nearest 5<\/td><td>=MROUND(A2,5)<\/td><\/tr><tr><td>Nearest 10<\/td><td>=MROUND(A2,10)<\/td><\/tr><tr><td>Nearest 50<\/td><td>=MROUND(A2,50)<\/td><\/tr><tr><td>Nearest 100<\/td><td>=MROUND(A2,100)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>These formulas will give us the same result as the previous ones.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"527\" height=\"140\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-different-values.png\" alt=\"\" class=\"wp-image-3936\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-different-values.png 527w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-different-values-300x80.png 300w\" sizes=\"(max-width: 527px) 100vw, 527px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes, displaying a very precise number is not useful and you may want to round the value. But there may be cases when&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[170,190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Round to Nearest Value in Excel<\/title>\n<meta name=\"description\" content=\"Learn to round numbers to the nearest value in Excel with examples. Download the rounding function file and start practicing. #ExcelTips #RoundingFunction\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Round to Nearest Value in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn to round numbers to the nearest value in Excel with examples. Download the rounding function file and start practicing. #ExcelTips #RoundingFunction\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-09T13:10:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-30T11:30:10+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Round to Nearest Value in Excel\",\"datePublished\":\"2019-01-09T13:10:47+00:00\",\"dateModified\":\"2024-03-30T11:30:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/\"},\"wordCount\":235,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"formulas\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/\",\"url\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/\",\"name\":\"Round to Nearest Value in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png\",\"datePublished\":\"2019-01-09T13:10:47+00:00\",\"dateModified\":\"2024-03-30T11:30:10+00:00\",\"description\":\"Learn to round numbers to the nearest value in Excel with examples. Download the rounding function file and start practicing. #ExcelTips #RoundingFunction\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Round to Nearest Value in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Round to Nearest Value in Excel","description":"Learn to round numbers to the nearest value in Excel with examples. Download the rounding function file and start practicing. #ExcelTips #RoundingFunction","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/","og_locale":"en_US","og_type":"article","og_title":"Round to Nearest Value in Excel","og_description":"Learn to round numbers to the nearest value in Excel with examples. Download the rounding function file and start practicing. #ExcelTips #RoundingFunction","og_url":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/","article_published_time":"2019-01-09T13:10:47+00:00","article_modified_time":"2024-03-30T11:30:10+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Round to Nearest Value in Excel","datePublished":"2019-01-09T13:10:47+00:00","dateModified":"2024-03-30T11:30:10+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/"},"wordCount":235,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png","keywords":["file","pinterest"],"articleSection":["formulas"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/","url":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/","name":"Round to Nearest Value in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png","datePublished":"2019-01-09T13:10:47+00:00","dateModified":"2024-03-30T11:30:10+00:00","description":"Learn to round numbers to the nearest value in Excel with examples. Download the rounding function file and start practicing. #ExcelTips #RoundingFunction","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/01\/round-to-the-nearest-value-example.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/formulas\/round-to-nearest-value\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Round to Nearest Value in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3932"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=3932"}],"version-history":[{"count":6,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3932\/revisions"}],"predecessor-version":[{"id":13759,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3932\/revisions\/13759"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=3932"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=3932"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=3932"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}