sumproduct disregarding 0 values

[waldymar]

Dear All,

I wish to calculate a weighted average indicator. However, some cells have either empty or zero values. I wish my indicator doesn't consider them in my weighted average. Any tips how to make it in SUMPRODUCT formula?
Thanks

 

[sanrv1f]

like this?

Excel Workbook
	B	C	D
4	51		55.15385
5	54
6	0
7	88
8	37
9	79
10	92
11	39
12	64
13	69
14	25
15	0
16	43
17	0
18	0
19	19
20	57
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D4	=SUM(B4:B20)/SUMPRODUCT(--(B4:B20<>0))

 

[Andrew Poulsom]

To return the number of cells in column A that contain a value greater than zero:

=COUNTIF(A:A,">0")

Use a formula like that as the divisor.

 

[waldymar]

To return the number of cells in column A that contain a value greater than zero:

=COUNTIF(A:A,">0")

Use a formula like that as the divisor.

what about this:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 width=64 align=right>5%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64 align=right>1000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 align=right>4%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>5000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 align=right>2%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>2000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=20 align=right>2.50%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>10000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 align=right>0%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>6000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 align=right>4%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>1000</TD></TR></TBODY></TABLE>
i need weighted average but disregarding 0%. something like: =sumproduct(A1:A6,B1:B6)/sum(B1:B6)

 

[Andrew Poulsom]

Is it?

=SUMPRODUCT(A1:A6,B1:B6)/SUMIF(A1:A6,">0",B1:B6)

which returns 3.05%.

 

[waldymar]

Is it?

=SUMPRODUCT(A1:A6,B1:B6)/SUMIF(A1:A6,">0",B1:B6)

which returns 3.05%.

Great. Appreciate a lot!