I’m working with a spreadsheet that has data like this:
Category | Value
X | 5
X | 7
X | 9
X | 11
Y | 3
Y | 5
I need to compute the average of the numbers in the Value column for each different Category. The result should look something like:
X | 8.0
Y | 4.0
What’s the best approach for this? Should I use the QUERY function or would pivot tables work better? I’ve tried a few different methods but can’t seem to get the grouping and averaging to work together properly. Any suggestions would be really helpful!
Both work fine, but AVERAGEIF is way more straightforward for simple stuff like this. Just make a summary table with your unique categories and use =AVERAGEIF($A:$A,"X",$B:$B) to get the average for category X. Swap “X” for cell references if you’re listing categories. I go with this method for smaller datasets since it’s easier to understand and tweak later. The formula’s also clearer than QUERY syntax, which gets messy with complex conditions. Plus you get better formatting control and can easily throw in other calculations next to your averages.
The QUERY function’s your best bet here. Use this formula:
=QUERY(A:B,"SELECT A, AVG(B) GROUP BY A")
This groups your categories and calculates the mean for each one. Way cleaner than pivot tables for this.
But honestly, if you’re doing this regularly, you’re wasting too much time in spreadsheets. I used to spend hours on similar tasks until I automated everything.
I set up workflows in Latenode that pull data from various sources, do the grouping and calculations automatically, then push results wherever I need them. Takes 10 minutes to set up once, then runs forever without me touching it.
The Google Sheets integration works great. It monitors your sheet, processes new data as it comes in, and generates reports automatically.
Beats manually running formulas every time your data changes.
pivot tables are def the way to go! just hit Insert > Pivot Table, throw your category in rows and values in the values area. it’ll calc the avg for you, and update when your data changes. no need to mess with formulas!