• 461K Members
• 5,587 Online
• 559K Conversations

New Contributor

# Show average of cells which display X text.

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

4 Replies

# Re: Show average of cells which display X text.

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.

# Re: Show average of cells which display X text.

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:

• Lookup value: your group rate name which is in my column A, your column D
• Table array: I selected the fields from my pivot table (be sure to add \$ before the row & column to hold the function to just those fields when you copy & paste the formula)
• Col Index #: the averages are found in the second column, so I entered 2
• Range lookup: I want an exact match, so I entered false

I hope this helps you accomplish what you're wanting to do!

# Re: Show average of cells which display X text.

thanks for quick response, will have a play round and see what works best.

# Re: Show average of cells which display X text.

The formula in M2, copied down to M7, is:
=SUMPRODUCT((D\$2:D\$7=D2)*K\$2:K\$7,L\$2:L\$7)/
SUMIF(D\$2:D\$7,D2,K\$2:K\$7)
The foregoing formula returns the weighted average, taking into consideration the maximum number of hours that can be claimed by each employee. The AVERAGE, AVERAGEIF, and AVERAGEIFS functions in Excel only return the simple average, ignoring any weighting factor.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies