| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
| Using the TODAY() function in a SUMIF function | Excel Worksheet Functions | |||
| How do I use the TODAY function with the SUMIF function? | Excel Worksheet Functions | |||
| SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
