Weited Avg exlcuding negative and zero value

[dennisli]

Let’s say a formula: =Sum(B1:B50)/Sum(A1:A50).
If there are any negatives or zero in B1:B50 or A1:A50, that record does not include in the calculation. For example, if A10 is -10 or 0, then A10 and B10 will be counted out even B10 is a positive number. Similarly, if B10 is -10 or 0, then A10 and B10 will be counted out even A10 is a positive number.
Thanks lot.
Dennis

 

[shg]

This is a guess as to what you want:

=SUMPRODUCT((A1:A50>0)*(B1:B50>0), B1:B50) / SUMPRODUCT((A1:A50>0)*(B1:B50>0), A1:A50)

 

[abmati]

I hope the following is ok for you.

Excel Workbook
	A	B	C
1	10	110	1
2	20	120	1
3	30	0	0
4	40	140	1
5		15	0
6	60	160	1
7	70	170	1
8	80	180	1
9	90	190	1
10	100	200	1
11	470	1270	2.702128
Sheet1

Regards.

Mati

 

[dennisli]

Thank you.
I am not sure whether I have posted before:
Suppose the date from row 1 to row 10 is for years 2000 to 2010. I want to average the latest three years positive results. In other words, first use A8:A10 data. If there are any positive or zero data, check range A7:B7, then A6:B6, until three pairs of positive data are found and get the average.
Appreciate your help.
Dennis