{"id":51691,"date":"2024-12-12T17:00:00","date_gmt":"2024-12-12T16:00:00","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=51691"},"modified":"2025-06-03T22:11:37","modified_gmt":"2025-06-03T20:11:37","slug":"casos-de-uso-da-matriz-principalformula-4","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/pt\/blog\/google-sheets-formulas\/master-arrayformula-4-use-cases\/","title":{"rendered":"Dominando ARRAYFORMULA: 4 casos de uso com exemplos"},"content":{"rendered":"\n[et_pb_section fb_built=&#8221;1&#8243; _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_row _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_column type=&#8221;4_4&#8243; _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_text _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>ARRAYFORMULA are valuable tools for managing spreadsheets, making it easier to handle complex calculations and automate repetitive tasks. Whether you\u2019re processing sales data, formatting dates, or applying conditional logic, ARRAYFORMULA can streamline your Workflows. This blog\u2019ll explore four practical use cases to help you unlock their potential.<\/p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221; sticky_enabled=&#8221;0&#8243;]<h2><b>1. Advanced calculations in reports<\/b><\/h2>\n<p>Scenario: A sales team must apply a 10% discount across multiple products. Manually doing this for each row is time-consuming, but an array formula can simplify the process.<\/p>\n<table>\n<tbody>\n<tr>\n<td>A<\/td>\n<td>B<\/td>\n<td>C<\/td>\n<\/tr>\n<tr>\n<td>Product<\/td>\n<td>Sales ($)<\/td>\n<td>Discounted Sales ($) (Using Array Formula)<\/td>\n<\/tr>\n<tr>\n<td>Product A<\/td>\n<td>100<\/td>\n<td>ARRAYFORMULA(B5:B7 * 0.9)<\/td>\n<\/tr>\n<tr>\n<td>Product B<\/td>\n<td>200<\/td>\n<td>180<\/td>\n<\/tr>\n<tr>\n<td>Product C<\/td>\n<td>300<\/td>\n<td>270<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/114LBEBJBoaSAL3gOGq88sMcYo7SRtPILyKwEkOcxDCE\/copy\" target=\"_blank\" rel=\"noopener\"><i>Copy this spreadsheet to check out the formula<\/i><\/a><\/p>\n<h3><b>How it works:<\/b><\/h3>\n<p>The array formula automatically multiplies each value in the sales column by 0.9 (10% discount), applying the calculation across the entire range.<\/p>\n<h3><b>Benefits:<\/b><\/h3>\n<ul>\n<li>Save time by processing multiple rows in a single step.<\/li>\n<li>Reduce errors caused by manual calculations.<\/li>\n<\/ul>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221; sticky_enabled=&#8221;0&#8243;]<h2><b>2. Extracting insights from dates<\/b><\/h2>\n<p>Scenario: You need to display the month and year for a set of dates in your report. Instead of converting each date manually, you can use an array formula.<\/p>\n<table>\n<thead>\n<tr>\n<th><span style=\"font-weight: 400;\">A<\/span><\/th>\n<th><span style=\"font-weight: 400;\">B<\/span><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Date<\/td>\n<td>Month-Year (Using Array Formula)<\/td>\n<\/tr>\n<tr>\n<td>01\/01\/2024<\/td>\n<td>ARRAYFORMULA(TEXT(A5:A11, &#8220;MMMM YYYY&#8221;))<\/td>\n<\/tr>\n<tr>\n<td>15\/02\/2024<\/td>\n<td>January 2024<\/td>\n<\/tr>\n<tr>\n<td>25\/03\/2024<\/td>\n<td>February 2024<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/114LBEBJBoaSAL3gOGq88sMcYo7SRtPILyKwEkOcxDCE\/copy\" target=\"_blank\" rel=\"noopener\"><i>Copy this spreadsheet to check out the formula<\/i><\/a><\/p>\n<h3><b>How it works:<\/b><\/h3>\n<p>The TEXT function, combined with an array formula, formats dates into readable month-year formats automatically.<\/p>\n<h3><b>Benefits:<\/b><\/h3>\n<ul>\n<li>Quickly transform dates for better readability.<\/li>\n<li>Prepare datasets for grouped reports or dashboards.<\/li>\n<\/ul>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; hover_enabled=&#8221;0&#8243; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221; sticky_enabled=&#8221;0&#8243;]<h2><b>3. Dynamic range referencing<\/b><\/h2>\n<p>Scenario: Calculate total expenses for items costing more than $100. Instead of manually filtering the data, an array formula dynamically applies the criteria.<\/p>\n<table>\n<tbody>\n<tr>\n<td>A<\/td>\n<td>B<\/td>\n<td>C<\/td>\n<\/tr>\n<tr>\n<td>Item<\/td>\n<td>Cost ($)<\/td>\n<td>Expenses ($)<\/td>\n<\/tr>\n<tr>\n<td>Chair<\/td>\n<td>120<\/td>\n<td>ARRAYFORMULA(SUMIF(B5:B7, &#8220;&gt;100&#8221;, B5:B7))<\/td>\n<\/tr>\n<tr>\n<td>Table<\/td>\n<td>150<\/td>\n<td>270<\/td>\n<\/tr>\n<tr>\n<td>Lamp<\/td>\n<td>90<\/td>\n<td>&#8211;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/114LBEBJBoaSAL3gOGq88sMcYo7SRtPILyKwEkOcxDCE\/copy\" target=\"_blank\" rel=\"noopener\"><i>Copy this spreadsheet to check out the formula<\/i> <\/a><\/p>\n<h3><b>How it works:<\/b><\/h3>\n<p>The array formula uses SUMIF to check costs exceeding $100 and sums the corresponding rows.<\/p>\n<h3><b>Benefits:<\/b><\/h3>\n<ul>\n<li>Automate criteria-based calculations.<\/li>\n<li>Ensure real-time updates when data changes.<\/li>\n<\/ul>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<h2><b>4. Transforming data for automation<\/b><\/h2>\n<p>Scenario: Classify students\u2019 scores as &#8220;Pass&#8221; or &#8220;Fail&#8221; based on a threshold of 50. An array formula makes it easy to apply this logic across the dataset.<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p>A<\/p>\n<\/td>\n<td>\n<p>B<\/p>\n<\/td>\n<td>\n<p>C<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Student<\/p>\n<\/td>\n<td>\n<p>Score<\/p>\n<\/td>\n<td>\n<p>Result (Using Array Formula)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Alex<\/p>\n<\/td>\n<td>\n<p>80<\/p>\n<\/td>\n<td>\n<p>ARRAYFORMULA(IF(B5:B7 &gt; 50, &#8220;Pass&#8221;, &#8220;Fail&#8221;))<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Jamie<\/p>\n<\/td>\n<td>\n<p>45<\/p>\n<\/td>\n<td>\n<p>Fail<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Sam<\/p>\n<\/td>\n<td>\n<p>90<\/p>\n<\/td>\n<td>\n<p>Pass<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/114LBEBJBoaSAL3gOGq88sMcYo7SRtPILyKwEkOcxDCE\/copy\" target=\"_blank\" rel=\"noopener\"><i>Copy this spreadsheet to check out the formula<\/i><\/a><\/p>\n<h3><b>How it works:<\/b><\/h3>\n<p>The formula evaluates each score, returning &#8220;Pass&#8221; for scores above 50 and &#8220;Fail&#8221; for those below the threshold.<\/p>\n<h3><b>Benefits:<\/b><\/h3>\n<ul>\n<li>Apply consistent logic to large datasets.<\/li>\n<li>Minimize manual classification errors.<\/li>\n<\/ul>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<h1><b>Simplify your workflows with ARRAYFORMULA<\/b><\/h1>\n<p>ARRAYFORMULA provides efficiency and flexibility for managing spreadsheets. You can automate calculations, handle dynamic data ranges, and solve real-world challenges by mastering these formulas.<\/p>\n<p>Start experimenting with ARRAYFORMULA today to streamline your processes and achieve more with your spreadsheets. Have a unique use case for ARRAYFORMULA? Share it in the comments\u2014we\u2019d love to hear your ideas!<\/p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"<p>ARRAYFORMULA are valuable tools for managing spreadsheets, making it easier to handle complex calculations and automate repetitive tasks. Whether you\u2019re processing sales data, formatting dates, or applying conditional logic, ARRAYFORMULA can streamline your Workflows. This blog\u2019ll explore four practical use cases to help you unlock their potential.1. Advanced calculations in reports Scenario: A sales team [&hellip;]<\/p>\n","protected":false},"author":46,"featured_media":44470,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[],"class_list":["post-51691","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/51691","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/46"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=51691"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/51691\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/44470"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=51691"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=51691"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=51691"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}