Feb 20 2019 12:55 PM
Hi
Please can you help create a formula for the below:..
In column M, what is the formula i would need if i wanted to take an average of column L, based on what text is based in column D.
I.e. M2, M3 & M4 are all Management and so Group Rate would be £23.37
M5, M6 & M7 are all Admin, so Group Rate would be £10.16.
Column D would be a free text box, so theoretically the 'group name' could be called anything - i would just want them grouped together.
THanks
Feb 20 2019 01:10 PM
You could use the AVERAGEIFS() function to accomplish this. Specifically, for your case M2 would look like =averageifs($K$2:$K$7,$D$2:$D$7,D2). You can then drag this cell down for the whole column.
You can look up the averageifs command for more information about how it is working.
Feb 20 2019 01:18 PM
Hi @philipo51617,
My recommendation would be to use a pivot table and VLOOKUP for this.
Start by selecting your data and inserting a pivot table in a new sheet. I'd use your column D data for the rows and an average of column L. By default, pivot tables use the sum, so you will need to update the Value Field Settings to average.
In column N, I would enter a VLOOKUP function. I made a very simplified version of your data in the screenshot below, but essentially:
I hope this helps you accomplish what you're wanting to do!
Feb 20 2019 01:35 PM
Feb 23 2019 01:49 AM