IF/SUMIF function
 

I can not figure this one out and it is driving me crazy!

This is my data:
2008 2009 Change 2008 - 2009
January 12.23 11.67 -0.56 -4.61%
February 11.90 12.54 0.64 5.37%
March 12.26 12.48 0.22 1.79%
April 11.98 12.15 0.17 1.41%
May 12.08 12.59 0.51 4.24%
June 11.98 12.61 0.63 5.22%
July 12.61 0.00 0.00 0.00%
August 12.20 0.00 0.00 0.00%
September 12.68 0.00 0.00 0.00%
October 12.16 0.00 0.00 0.00%
November 12.27 0.00 0.00 0.00%
December 12.54 0.00 0.00 0.00%

I am trying to make a formula that will do the following:
add up the values in column C but only include the data that is not $0 (so
right now it is through June) and then add up the corresponding values in
column B (so only through June) and divide the sum of C by the sum of B, then
subtract 1. It has to look at all the data so that when we do populate data
for July, the formula will update itself to include that data.

Any help on this would be most appreciated!

IF/SUMIF function
 

Try

=SUM(C2:C20)/SUMIF(C2:C20,"<0",B2:B20)-1

--
__________________________________
HTH

Bob

"Denise_NOLA" wrote in message
...
I can not figure this one out and it is driving me crazy!

This is my data:
2008 2009 Change 2008 - 2009
January 12.23 11.67 -0.56 -4.61%
February 11.90 12.54 0.64 5.37%
March 12.26 12.48 0.22 1.79%
April 11.98 12.15 0.17 1.41%
May 12.08 12.59 0.51 4.24%
June 11.98 12.61 0.63 5.22%
July 12.61 0.00 0.00 0.00%
August 12.20 0.00 0.00 0.00%
September 12.68 0.00 0.00 0.00%
October 12.16 0.00 0.00 0.00%
November 12.27 0.00 0.00 0.00%
December 12.54 0.00 0.00 0.00%

I am trying to make a formula that will do the following:
add up the values in column C but only include the data that is not $0 (so
right now it is through June) and then add up the corresponding values in
column B (so only through June) and divide the sum of C by the sum of B,
then
subtract 1. It has to look at all the data so that when we do populate
data
for July, the formula will update itself to include that data.

Any help on this would be most appreciated!

IF/SUMIF function
 

Thanks Bob! That works great.

"Bob Phillips" wrote:

Try

=SUM(C2:C20)/SUMIF(C2:C20,"<0",B2:B20)-1

--
__________________________________
HTH

Bob

"Denise_NOLA" wrote in message
...
I can not figure this one out and it is driving me crazy!

This is my data:
2008 2009 Change 2008 - 2009
January 12.23 11.67 -0.56 -4.61%
February 11.90 12.54 0.64 5.37%
March 12.26 12.48 0.22 1.79%
April 11.98 12.15 0.17 1.41%
May 12.08 12.59 0.51 4.24%
June 11.98 12.61 0.63 5.22%
July 12.61 0.00 0.00 0.00%
August 12.20 0.00 0.00 0.00%
September 12.68 0.00 0.00 0.00%
October 12.16 0.00 0.00 0.00%
November 12.27 0.00 0.00 0.00%
December 12.54 0.00 0.00 0.00%

I am trying to make a formula that will do the following:
add up the values in column C but only include the data that is not $0 (so
right now it is through June) and then add up the corresponding values in
column B (so only through June) and divide the sum of C by the sum of B,
then
subtract 1. It has to look at all the data so that when we do populate
data
for July, the formula will update itself to include that data.

Any help on this would be most appreciated!