If you use an array formula, such as query(), in a cell located near other cells that already have values in them and the formula returns results that would spill over into the preexisting cells, you'll get an error.
All of the other values in the cells that are contiguously located with cell G2 get generated automatically.īe aware of one problem that stumps a lot of people who don't have prior experience with Google Sheet's array formulas when they first start using them. If you look in cell G2, you'll see that formula. =query(A2:B8, "select A, avg(B) group by A", 1)
However, if you are not familiar with SQL, it's a very intuitive, easy-to-learn language.įor example, if you wanted the average of each group instead of the sum, you could copy the formula in cell D2 and edit it as follows. If you're already familiar with SQL, then you already know how to use the query() function. =query(A2:B8, "select A, sum(B) group by A label sum(B) 'sum'", 1) This cell contains the following formula. To follow up, in case you have trouble understanding how to use the query function, take a look at the contents of cell D2 in my spreadsheet.
Recent ClippyPoint Milestones !Ĭongratulations and thank you to these contributors DateĪ community since MaDownload the official /r/Excel Add-in to convert Excel cells into a table that can be posted using reddit's markdown. Include a screenshot, use the tableit website, or use the ExcelToReddit converter (courtesy of u/tirlibibi17) to present your data. NOTE: For VBA, you can select code in your VBA window, press Tab, then copy and paste that into your post or comment. To keep Reddit from mangling your formulas and other code, display it using inline-code or put it in a code-block This will award the user a ClippyPoint and change the post's flair to solved. OPs can (and should) reply to any solutions with: Solution Verified
0 kommentar(er)
