Hi,
I think I am using the correct formula for my scenario. I definitely need some guidance.
I need to calculate the average of a range of cells containing different criteria. Attached is a sample of what I am looking for. I think AVERAGEIFS is the formula to use, but could (likely) be wrong.
=AVERAGEIFS(B2:B7,A2:A7,"CD",A2:A7,"DD1",A2:A7,"DD2")
The average being displayed in cell F2 in the attached sample file.
Anyone's help is much appreciated.
Cheers,
Ashley
Welcome to the forum.
No, it's not quite right here because the formula wants all the criteria met at once - it's an AND rather than an OPR statement. Try this instead:
=SUM(AVERAGEIFS(B2:B7,A2:A7,{"CD","DD1","DD2"}))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks for the response.
That formula gives me the SUM of the AVERAGES for the values for CD, DD1, DD2. I need it to show the AVERAGE only of CD, DD1, DD2.
I'm not sure because sum of average is not average such as average of average is not average. But you are right there was AND condition in the AVERAGEIFS function.Originally Posted by AliGW
=SUM(SUMIF(A2:A7,{"CD","DD1","DD2"},B2:B7))/SUM(COUNTIF(A2:A7,{"CD","DD1","DD2"}))
or
=AVERAGE(IF(ISNUMBER(MATCH(A2:A7,{"CD","DD1","DD2"};)),B2:B7))
Last edited by BMV; 01-14-2020 at 04:10 AM.
BMV! That worked!
Many thanks to you both!
In the workbook example that was posted then of course the staightforward = AVERAGE(A2:A7) is sufficient.
I'm assuming in the production workbook you have values in A & B that you don't want included, and if so when you pose a question please maje sure your sample data accurately reflects your production data.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks for the feedback. I did have have data in my workbook I needed to exclude, and this wasn't reflected in my example. I will take that on board for any future queries.
Cheers,
Ashley
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks