averaging different cells from specified categories

[bob hanle]

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

 

[Aladin Akyurek]

So the question is: Can I write a "sumproduct" formula that utilizes the data in Row 2 for "total trades" data in the separate columns of B, F, & J for a weighted average in the same row "for WR%" columns C, G, & K?

You want a single result (weighted average) for:

Net	Total	WR%	Profit	Net	Total	WR%	Profit	Net	Total	WR%	Profit
Profit	Trades	Profit	Factor	Profit	Trades	Profit	Factor	Profit	Trades	Profit	Factor
	308	62.66			65	46.15			42	76.19
	295	64.41			73	57.53			27	51.85
	319	63.64			68	70.59			43	48.84
	309	63.11			76	53.95			32	78.13

<tbody>
</tbody>

Right?

 

[bob hanle]

You want a single result (weighted average) for:

Net	Total	WR%	Profit	Net	Total	WR%	Profit	Net	Total	WR%	Profit
Profit	Trades	Profit	Factor	Profit	Trades	Profit	Factor	Profit	Trades	Profit	Factor
	308	62.66			65	46.15			42	76.19
	295	64.41			73	57.53			27	51.85
	319	63.64			68	70.59			43	48.84
	309	63.11			76	53.95			32	78.13

<tbody>
</tbody>

Right?

I want a weighted average for each row of data, because each row represents a unique category, not all the data combined. Thanks!

 

[Aladin Akyurek]

I want a weighted average for each row of data, because each row represents a unique category, not all the data combined. Thanks!

Net	Total	WR%	Profit	Net	Total	WR%	Profit	Net	Total	WR%	Profit	Weighted
Profit	Trades	Profit	Factor	Profit	Trades	Profit	Factor	Profit	Trades	Profit	Factor	Average
	308	62.66			65	46.15			42	76.19		61.44339759
	295	64.41			73	57.53			27	51.85		62.27997468
	319	63.64			68	70.59			43	48.84		63.25906977
	309	63.11			76	53.95			32	78.13		62.59316547

<tbody>
</tbody>

M4, copy down:

=SUMPRODUCT(CHOOSE({1,2,3},B4,F4,J4),CHOOSE({1,2,3},C4,G4,K4)/SUM(B4,F4,J4))

 

[bob hanle]

Net	Total	WR%	Profit	Net	Total	WR%	Profit	Net	Total	WR%	Profit	Weighted
Profit	Trades	Profit	Factor	Profit	Trades	Profit	Factor	Profit	Trades	Profit	Factor	Average
	308	62.66			65	46.15			42	76.19		61.44339759
	295	64.41			73	57.53			27	51.85		62.27997468
	319	63.64			68	70.59			43	48.84		63.25906977
	309	63.11			76	53.95			32	78.13		62.59316547

<tbody>
</tbody>

M4, copy down:

=SUMPRODUCT(CHOOSE({1,2,3},B4,F4,J4),CHOOSE({1,2,3},C4,G4,K4)/SUM(B4,F4,J4))[/QUOTE

I haven't tried the formula yet, but it looks like exactly what I need. I'll let you know how it turns out. I assume 1,2,& 3 in the brackets are constants in the formula. Okay, I'm a little out of the loop but what does "M4" mean? Sorry about that stupid question.

 

[bob hanle]

Aladin, once again you came through brilliantly. It works perfectly on my charts. Many, many, thanks again for your tutoring and creating the formulas for me. It's a real pleasure to have you help me in learning to use excel. I can't thank you enough for not only your expertise but for your prompt responses. Bob Hanle

 

[Aladin Akyurek]

Aladin, once again you came through brilliantly. It works perfectly on my charts. Many, many, thanks again for your tutoring and creating the formulas for me. It's a real pleasure to have you help me in learning to use excel. I can't thank you enough for not only your expertise but for your prompt responses. Bob Hanle

You are welcome. Thanks for the kind feedback.