Syntax<\/h3>\n
DAVERAGE(database, field, criteria)<\/span><\/strong><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let us assume that the below list of foods is our secret diet suggested by a nutritionist. We will try our hands with a few examples.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/DAVERAGE-formula-1.png” alt=”DAVERAGE formula 1″ title_text=”DAVERAGE formula 1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n We see that the data set is in a labeled tabular form spanning across cells A4 through to G13. And the criteria is keyed in the cells A1 through to G2.<\/p>\n Please note from the cases illustrated above that we can specify one or more criteria to filter the averaging process. For example, the case in row # 7 averages the values in the \u201cCarbs (g)\u201d column, subject to the criteria in the cell D2. Whereas the case in row # 10 averages the values from \u201cFat (g)\u201d column, subject to the criteria within the cells C2 and D2. The case on row # 11 includes all the criteria from A2 through to G2.<\/p>\n The case in row # 12 is a rather interesting one! We didn\u2019t specify anything in the cell G2, yet, we gave that reference to the DAVERAGE formula. The result is 4.50 because it averaged everything in the \u201cFiber (g)\u201d column as there are no criteria to filter out the rows.<\/p>\n Now, let us talk about a pitfall. Here is the first one, which we might already be aware of. Please consider the first case in the snapshot below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/DAVERAGE-formula-2.png” alt=”DAVERAGE formula 2″ title_text=”DAVERAGE formula 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here, we tried averaging non-numeric values! Just like the\u00a0<\/span>AVERAGE<\/a>\u00a0<\/span>formula, DAVERAGE formula averages only numbers. Otherwise, it returns #DIV\/0! error.<\/p>\n Here\u2019s the second one in the image below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/DAVERAGE-formula-3.png” alt=”DAVERAGE formula 3″ title_text=”DAVERAGE formula 3″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Seemingly there\u2019s nothing wrong with the formula this time but the result isn\u2019t what we are expecting. The problem lies within the labels though. Apparently, a missing or a mismatched label doesn\u2019t help the DAVERAGE formula, as its working hinges on the field name that the second parameter takes. The labels didn\u2019t match, so it evaluates zero instances of a criteria match, which implies a divide by zero occurrences, hence the error.<\/span><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The\u00a0DAVERAGE\u00a0formula in Google Sheets is almost similar to that of the\u00a0AVERAGEformula, but with a distinction. It gives us the average of values available in a table like range, that meet a specified criteria. This is analogous to an SQL database sum query. The \u2018D\u2019 in the DAVERAGE stands for \u2018Database\u2019, therefore, we can call this […]<\/p>\n","protected":false},"author":40,"featured_media":4010,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The DAVERAGE<\/a> formula in Google Sheets is almost similar to that of the AVERAGE<\/a> formula, but with a distinction. It gives us the average of values available in a table like range, that meet a specified criteria. This is analogous to an SQL database sum query. The \u2018D\u2019 in the DAVERAGE stands for \u2018Database\u2019, therefore, we can call this a Database AVERAGE formula.\n\n
Usage: DAVERAGE Formula<\/h3>\n
Unwarranted Case<\/h5>\n
Syntax<\/h3>\nDAVERAGE(database, field, criteria)<\/span><\/strong>\n
\n \t
Usage: DAVERAGE Formula<\/h3>\nLet us assume that the below list of foods is our secret diet suggested by a nutritionist. We will try our hands with a few examples.\n\n
![\"DAVERAGE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-49.png\")
\n\nWe see that the data set is in a labeled tabular form spanning across cells A4 through to G13. And the criteria is keyed in the cells A1 through to G2.\n\nPlease note from the cases illustrated above that we can specify one or more criteria to filter the averaging process. For example, the case in the row # 7 averages the values in the \u201cCarbs (g)\u201d column, subject to the criteria in the cell D2. Whereas the case in row # 10 averages the values from \u201cFat (g)\u201d column, subject to the criteria within the cells C2 and D2. The case on row # 11 includes all the criteria from A2 through to G2.\n\nThe case on the row # 12 is a rather interesting one! We didn\u2019t specify anything in the cell G2, yet, we gave that reference to the DAVERAGE formula. The result is 4.50 because it averaged everything in the \u201cFiber (g)\u201d column as there are no criteria to filter out the rows.\nUnwarranted Case<\/h5>\nNow, let us talk about a pitfall. Here is the first one, which we might already be aware of. Please consider the first case in the snapshot below.\n\n
![\"DAVERAGE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-50.png\")
\n\nHere, we tried averaging non-numeric values! Just like the AVERAGE<\/a> formula, DAVERAGE formula averages only numbers. Otherwise, it returns #DIV\/0! error.\n\nHere\u2019s the second one in the image below.\n\n![\"DAVERAGE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-51.png\")
\n\nSeemingly there\u2019s nothing wrong with the formula this time but the result isn\u2019t what we are expecting. The problem lies within the labels though. Apparently, a missing or a mismatched label doesn\u2019t help the DAVERAGE formula, as its working hinges on the field name that the second parameter takes. The labels didn\u2019t match, so it evaluates zero instances of a criteria match, which implies a divide by zero occurrences, hence the error.\n\n ","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3851","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/3851","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=3851"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/3851\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/4010"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=3851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=3851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=3851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}