{"id":112224,"date":"2021-03-16T17:33:31","date_gmt":"2021-03-16T12:03:31","guid":{"rendered":"https:\/\/www.vskills.in\/certification\/tutorial\/?page_id=112224"},"modified":"2024-04-12T14:31:48","modified_gmt":"2024-04-12T09:01:48","slug":"formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2","status":"publish","type":"page","link":"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/","title":{"rendered":"Formulae, Cell referencing (absolute &#038; relative) and worksheet ranges"},"content":{"rendered":"\n<p><strong>Cell Range and Referencing<\/strong><\/p>\n\n\n\n<p>A range is two or more cells which can be adjacent (all cells are along side) or non-adjacent. Adjacent ranges are given as<\/p>\n\n\n\n<p>Start cell address: End cell address Eg. A1:A3 or A1:B3<\/p>\n\n\n\n<p>Adjacent range can be selected by mouse by dragging from start to last and pressing control key for large ranges to scroll across the sheet. R1C1 reference style is used for row (R1) and column(C1).<\/p>\n\n\n\n<p>Range Names \u2013 Adjacent ranges can be given names for easy access. Name is given in cell address box in the formula bar. Cell address in formula can be of two types relative and absolute reference.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Relative Reference &#8211; It is in relation to the position of the cell that contains the formula and automatic adjustment is done like if cell A3 has the formula =A1+A2 and if cell A3 is copied to cell B3, the formula in cell B3 becomes =B1+B2.<\/li><li>Absolute Reference &#8211; Reference to cell or range does not change when the formula is copied. A $ (dollar sign) character before the column or row is added like if cell A3 has the formula =$A$1+A2 and if cell A3 is copied to cell B3, the formula in cell B3 becomes =$A$1+B2.<\/li><\/ul>\n\n\n\n<p>Formulae is the most used facility of MS-EXCEL for calculating values to return a result. Each Excel formula must have three key elements: the equal sign (=) that signifies start of a formula, the values or cell references to be calculated, and the mathematical operators, such as a plus sign (+) for addition or a minus sign (-) for subtraction. Operators act on data in formula to give result as<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><td><strong>Operator<\/strong><\/td><td><strong>What It Does<\/strong><\/td><td><strong>Example Formula<\/strong><\/td><td><strong>Result<\/strong><\/td><\/tr><\/thead><tbody><tr><td>+<\/td><td>Addition<\/td><td>=2+2<\/td><td>4<\/td><\/tr><tr><td>&#8211;<\/td><td>Subtraction<\/td><td>=2-2<\/td><td>0<\/td><\/tr><tr><td>*<\/td><td>Multiplication<\/td><td>=2*2<\/td><td>4<\/td><\/tr><tr><td>\/<\/td><td>Division<\/td><td>=2\/2<\/td><td>1<\/td><\/tr><tr><td>=<\/td><td>Equal to<\/td><td>=A2=A3<\/td><td>True\/False<\/td><\/tr><tr><td>&lt;<\/td><td>Less than<\/td><td>=2&lt;3 or =A3&lt;A5<\/td><td>True<\/td><\/tr><tr><td>&gt;<\/td><td>Greater than<\/td><td>=2&gt;3<\/td><td>False<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>A formula is defined as, a sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. In case of multiple operators in a formula, different operators have different importance or precedence and are executed before others as<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Operations enclosed in parentheses<\/li><li>Multiplication and division operations<\/li><li>Addition and subtraction operations<\/li><\/ul>\n\n\n\n<p><strong>Functions<\/strong><br>They are in-built formulas which do specific task and given as<br>Function name (input1, input2\u2026)<br>Function name is a specific function name and input are as per data needed by formulae. Different types of functions are mathematical, statistical, date and time, logical and string functions. Different types of functions are mathematical, statistical, date and time, logical and string functions like<br>ROUND Function &#8211; Rounds a number to a specified number of digits.<br>ROUND (number, num_digits)<br>Number is number to round and Num_digits gives number of digits to round upto.<br>COUNT Function &#8211; Counts number of cells that contain numbers<br>COUNT (value1, value2&#8230;)<br>Value1, value2, &#8230; are cell address.<br>MAX Function &#8211; Returns the largest value in a set of values. Its usage is<br>MAX (number1, number2&#8230;)<br>MIN Function &#8211; Returns the smallest number in a set of values. Its usage is<br>MIN (number1, number2&#8230;)<br>AVERAGE Function \u2013 Returns average of numbers given<br>=Average (number1, number2, address1:address2)<br>SUM function &#8211; To calculate the sum of the values in the cells E3 to E6 the formula would be =SUM (E3:E6) or =E3+E4+E5+E6, the difference between the two is that in earlier on we have taken a range (a collection of consecutive cells) and calculated the sum by using the sum function<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Cell Range and Referencing A range is two or more cells which can be adjacent (all cells are along side) or non-adjacent. Adjacent ranges are given as Start cell address: End cell address Eg. A1:A3 or A1:B3 Adjacent range can be selected by mouse by dragging from start to last and pressing control key for&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-112224","page","type-page","status-publish","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Formulae, Cell referencing (absolute &amp; relative) and worksheet ranges - Tutorial<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Formulae, Cell referencing (absolute &amp; relative) and worksheet ranges - Tutorial\" \/>\n<meta property=\"og:description\" content=\"Cell Range and Referencing A range is two or more cells which can be adjacent (all cells are along side) or non-adjacent. Adjacent ranges are given as Start cell address: End cell address Eg. A1:A3 or A1:B3 Adjacent range can be selected by mouse by dragging from start to last and pressing control key for...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Tutorial\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/vskills.in\/\" \/>\n<meta property=\"article:modified_time\" content=\"2024-04-12T09:01:48+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/\",\"name\":\"Formulae, Cell referencing (absolute & relative) and worksheet ranges - Tutorial\",\"isPartOf\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#website\"},\"datePublished\":\"2021-03-16T12:03:31+00:00\",\"dateModified\":\"2024-04-12T09:01:48+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Formulae, Cell referencing (absolute &#038; relative) and worksheet ranges\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#website\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\",\"name\":\"Tutorial\",\"description\":\"Vskills - A initiative in elearning and certification\",\"publisher\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.vskills.in\/certification\/tutorial\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#organization\",\"name\":\"Vskills\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg\",\"contentUrl\":\"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg\",\"width\":73,\"height\":55,\"caption\":\"Vskills\"},\"image\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/vskills.in\/\",\"https:\/\/x.com\/vskills_in\",\"https:\/\/www.linkedin.com\/company-beta\/1371554\/\",\"https:\/\/www.youtube.com\/channel\/UCMWnscxPwRF_PqXo9B7q_Tw\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Formulae, Cell referencing (absolute & relative) and worksheet ranges - Tutorial","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:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/","og_locale":"en_US","og_type":"article","og_title":"Formulae, Cell referencing (absolute & relative) and worksheet ranges - Tutorial","og_description":"Cell Range and Referencing A range is two or more cells which can be adjacent (all cells are along side) or non-adjacent. Adjacent ranges are given as Start cell address: End cell address Eg. A1:A3 or A1:B3 Adjacent range can be selected by mouse by dragging from start to last and pressing control key for...","og_url":"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/","og_site_name":"Tutorial","article_publisher":"https:\/\/www.facebook.com\/vskills.in\/","article_modified_time":"2024-04-12T09:01:48+00:00","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/","url":"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/","name":"Formulae, Cell referencing (absolute & relative) and worksheet ranges - Tutorial","isPartOf":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#website"},"datePublished":"2021-03-16T12:03:31+00:00","dateModified":"2024-04-12T09:01:48+00:00","breadcrumb":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/formulae-cell-referencing-absolute-relative-and-worksheet-ranges-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.vskills.in\/certification\/tutorial\/"},{"@type":"ListItem","position":2,"name":"Formulae, Cell referencing (absolute &#038; relative) and worksheet ranges"}]},{"@type":"WebSite","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#website","url":"https:\/\/www.vskills.in\/certification\/tutorial\/","name":"Tutorial","description":"Vskills - A initiative in elearning and certification","publisher":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.vskills.in\/certification\/tutorial\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#organization","name":"Vskills","url":"https:\/\/www.vskills.in\/certification\/tutorial\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/","url":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg","contentUrl":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg","width":73,"height":55,"caption":"Vskills"},"image":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/vskills.in\/","https:\/\/x.com\/vskills_in","https:\/\/www.linkedin.com\/company-beta\/1371554\/","https:\/\/www.youtube.com\/channel\/UCMWnscxPwRF_PqXo9B7q_Tw"]}]}},"_links":{"self":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/112224","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/comments?post=112224"}],"version-history":[{"count":1,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/112224\/revisions"}],"predecessor-version":[{"id":112225,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/112224\/revisions\/112225"}],"wp:attachment":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/media?parent=112224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/categories?post=112224"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/tags?post=112224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}