I want to average cells in different columns which meet a minimum criteria, i.e. > 2.0, from specified categories list in the first column. Example: I want to select a category listed in column A and average the numbers listed in columns C and F for that category which meet a minimum threshold. Thanks
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
averaging different cells from specified categories
- Thread starter bob hanle
- Start date
Chris Macro
Well-known Member
- Joined
- Nov 2, 2011
- Messages
- 1,345
- Office Version
- 365
- Platform
- Windows
Have you looked at writing an AverageIf formula? It's hard to provide you with an example formula unless you provide more specifics to the data you're working with.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
=AVERAGE(IF(A3:A8="a",IF(C3:C8>2,C3:C8)),IF(A3:A8="a",IF(F3:F8>2,F3:F8)))
where "a" is a category which must hold for the A-range.
Upvote
0
That's perfect. Many thanks! Now if I want to add another category such as "b" in the formula, do I insert that in quotes right after "a" following the comma, or do I have to repeat A3:A8="b" again.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
That would mean having a list: Control+shift+enter...
Rich (BB code):
=AVERAGE(IF(ISNUMBER(MATCH(A3:A8,{"a","b"},0)),IF(C3:C8>2,C3:C8)),
IF(ISNUMBER(MATCH(A3:A8,{"a","b"},0)),IF(F3:F8>2,F3:F8)))
You can also have the categories, say, in X2:X3, and invoke:
Rich (BB code):
=AVERAGE(IF(ISNUMBER(MATCH(A3:A8,X2:X3,0)),IF(C3:C8>2,C3:C8)),
IF(ISNUMBER(MATCH(A3:A8,X2:X3,0)),IF(F3:F8>2,F3:F8)))
Upvote
0
Chris Macro
Well-known Member
- Joined
- Nov 2, 2011
- Messages
- 1,345
- Office Version
- 365
- Platform
- Windows
I believe you would want to add an OR() function.....for example:
=AVERAGE(IF(OR(A3:A8="a",A3:A8="b"),IF(C3:C8>2,C3:C8)),IF(OR(A3:A8="a",A3:A8="b"),IF(F3:F8>2,F3:F8)))
=AVERAGE(IF(OR(A3:A8="a",A3:A8="b"),IF(C3:C8>2,C3:C8)),IF(OR(A3:A8="a",A3:A8="b"),IF(F3:F8>2,F3:F8)))
Upvote
0
Aladin, you were right on. Many,many thanks for your advice. It worked perfectly using the control+shift+enter for the match. I was able to write a formula which covered 2 categories and 4 columns of data which required 232 characters and worked brilliantly. Thanks again for your incredible help considering I'm somewhat new to writing excel formulas. Kudos to you and to MrExcel.com. Bob
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
You are welcome. Thanks for the kind feedback.
Upvote
0
I'm back! Now I have a different question. I want to obtain a weighted average of data in different cells, not cells adjacent to each other. For example how do I get a weighted average for cells in B2, D2, and F2, with data in cells C2, E2, and G2. Is that possible? Thanks!
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Would you elaborate a bit in full extenso? Perhaps using a small sample...
Upvote
0
