I'm new to excel functions and I've been asked to calculate the average sales for every state using the "Subtotal" function.
The dataset is like this:
| State | Sales |
|---|---|
| California | 22,5 |
| Utah | 75 |
| Utah | 122,4 |
| California | 99 |
| Texas | 101,3 |
| Indiana | 47 |
| Texas | 136 |
Can anyone help me?
CodePudding user response：
I'm not sure Subtotal is the best fit here. My first instinct would be to use a pivot table. You can also get the same answers with dynamic array formulas.
Formula in D3:
=SORT(UNIQUE(StatesTable[State]))
Formula in E3:
=SUMIFS(StatesTable[Sales],StatesTable[State],D3#)/COUNTIFS(StatesTable[State],D3#)