I have a sample dataset of a few stores located within their districts and sales attributed to them. I want to create two new columns 'Avg_District_Sales', which calculates the avg sales of each stores within its district and for both the years separately and 'Store_District_Rank', which ranks the stores based on the value of sales within its district and for both the years separately. For example, in the attached sample dataset, stores 101, 105, and 106 belong to district 1, so for each of these stores, 'Avg_District_Sales' column will perform an average of their sales, i.e., 459, 779, 540 and display 592.6 against each of these stores for the year 2022. It would do the same for year 2023. Store district rank will also be calculated on the same concept. I'm aware of averageifs() function but I'm not sure how can I use it to display the values against each store.
The yellow highlighted columns in the attached sample workbook are my desired columns that I need to calculate.
Bookmarks