Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula or use a macro?
I'm trying to come up with a formula that will sum the values in any of these
columns based on the year column. For example, I have the following formula that sums the values in the Create column if the Year column = 2002. The second part of the formula needs to then count how many numbers were added so that an average can be calculated. That's the part I cannot figure out because I need to only count cells with a value 0 for whatever year I use in the formula. Can this be done via a formula or do I have to resort to a macro? I wouldn't be surprised if someone told me a totally different formula will get the result I need. Thanks! Randy =SUMIF($R2:$R6,"2002",M2:M6)/COUNTIF(M2:M6,"0") CREATE ESTIMATE APPROVE PENDING ACTIVE COMPLETE YEAR 284.69 2002 52.34 721.87 2003 52.31 79.73 263.14 2002 230.25 515.83 2002 2002 34.96 45.09 0.17 2002 42.16 3.02 35.02 190.88 256.52 2004 54.08 8.81 20.94 395.09 475.49 2002 54.08 8.8 20.94 395.13 475.49 2005 54.08 8.82 5.99 56 419.24 293.49 2002 54.07 8.81 6 410.11 370.48 2002 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula or use a macro?
Try:
=SUMPRODUCT(--(G2:G12=2002),--(A2:A120),(A2:A12))/SUMPRODUCT(--(G2:G12=2002),--(A2:A120)) "Randy" wrote: I'm trying to come up with a formula that will sum the values in any of these columns based on the year column. For example, I have the following formula that sums the values in the Create column if the Year column = 2002. The second part of the formula needs to then count how many numbers were added so that an average can be calculated. That's the part I cannot figure out because I need to only count cells with a value 0 for whatever year I use in the formula. Can this be done via a formula or do I have to resort to a macro? I wouldn't be surprised if someone told me a totally different formula will get the result I need. Thanks! Randy =SUMIF($R2:$R6,"2002",M2:M6)/COUNTIF(M2:M6,"0") CREATE ESTIMATE APPROVE PENDING ACTIVE COMPLETE YEAR 284.69 2002 52.34 721.87 2003 52.31 79.73 263.14 2002 230.25 515.83 2002 2002 34.96 45.09 0.17 2002 42.16 3.02 35.02 190.88 256.52 2004 54.08 8.81 20.94 395.09 475.49 2002 54.08 8.8 20.94 395.13 475.49 2005 54.08 8.82 5.99 56 419.24 293.49 2002 54.07 8.81 6 410.11 370.48 2002 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula or use a macro?
That worked great! Would you mind explaning how the various parts work? I'm
very good with advanced Lookups, Nested IFs, etc... but this is foreign to me. Thanks again! "Toppers" wrote: Try: =SUMPRODUCT(--(G2:G12=2002),--(A2:A120),(A2:A12))/SUMPRODUCT(--(G2:G12=2002),--(A2:A120)) "Randy" wrote: I'm trying to come up with a formula that will sum the values in any of these columns based on the year column. For example, I have the following formula that sums the values in the Create column if the Year column = 2002. The second part of the formula needs to then count how many numbers were added so that an average can be calculated. That's the part I cannot figure out because I need to only count cells with a value 0 for whatever year I use in the formula. Can this be done via a formula or do I have to resort to a macro? I wouldn't be surprised if someone told me a totally different formula will get the result I need. Thanks! Randy =SUMIF($R2:$R6,"2002",M2:M6)/COUNTIF(M2:M6,"0") CREATE ESTIMATE APPROVE PENDING ACTIVE COMPLETE YEAR 284.69 2002 52.34 721.87 2003 52.31 79.73 263.14 2002 230.25 515.83 2002 2002 34.96 45.09 0.17 2002 42.16 3.02 35.02 190.88 256.52 2004 54.08 8.81 20.94 395.09 475.49 2002 54.08 8.8 20.94 395.13 475.49 2005 54.08 8.82 5.99 56 419.24 293.49 2002 54.07 8.81 6 410.11 370.48 2002 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula or use a macro?
Don,
This works as well but my real data is several thousand rows and the AVERAGE statement is limited to 20. Randy "Don Guillett" wrote: This is an array formula that must be entered/edited using ctrl+shift+enter vs just enter =AVERAGE(IF((D2:D6=2002)*(E2:E60),E2:E6)) -- Don Guillett SalesAid Software "Randy" wrote in message ... I'm trying to come up with a formula that will sum the values in any of these columns based on the year column. For example, I have the following formula that sums the values in the Create column if the Year column = 2002. The second part of the formula needs to then count how many numbers were added so that an average can be calculated. That's the part I cannot figure out because I need to only count cells with a value 0 for whatever year I use in the formula. Can this be done via a formula or do I have to resort to a macro? I wouldn't be surprised if someone told me a totally different formula will get the result I need. Thanks! Randy =SUMIF($R2:$R6,"2002",M2:M6)/COUNTIF(M2:M6,"0") CREATE ESTIMATE APPROVE PENDING ACTIVE COMPLETE YEAR 284.69 2002 52.34 721.87 2003 52.31 79.73 263.14 2002 230.25 515.83 2002 2002 34.96 45.09 0.17 2002 42.16 3.02 35.02 190.88 256.52 2004 54.08 8.81 20.94 395.09 475.49 2002 54.08 8.8 20.94 395.13 475.49 2005 54.08 8.82 5.99 56 419.24 293.49 2002 54.07 8.81 6 410.11 370.48 2002 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula or use a macro?
Take a look he
http://www.mcgimpsey.com/excel/doubleneg.html In article , Randy wrote: That worked great! Would you mind explaning how the various parts work? I'm very good with advanced Lookups, Nested IFs, etc... but this is foreign to me. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula or use a macro?
Randy,
AVERAGE is NOT limited to 20 rows but 20 "arguments" so Don's formula is perfectly usable. "Randy" wrote: Don, This works as well but my real data is several thousand rows and the AVERAGE statement is limited to 20. Randy "Don Guillett" wrote: This is an array formula that must be entered/edited using ctrl+shift+enter vs just enter =AVERAGE(IF((D2:D6=2002)*(E2:E60),E2:E6)) -- Don Guillett SalesAid Software "Randy" wrote in message ... I'm trying to come up with a formula that will sum the values in any of these columns based on the year column. For example, I have the following formula that sums the values in the Create column if the Year column = 2002. The second part of the formula needs to then count how many numbers were added so that an average can be calculated. That's the part I cannot figure out because I need to only count cells with a value 0 for whatever year I use in the formula. Can this be done via a formula or do I have to resort to a macro? I wouldn't be surprised if someone told me a totally different formula will get the result I need. Thanks! Randy =SUMIF($R2:$R6,"2002",M2:M6)/COUNTIF(M2:M6,"0") CREATE ESTIMATE APPROVE PENDING ACTIVE COMPLETE YEAR 284.69 2002 52.34 721.87 2003 52.31 79.73 263.14 2002 230.25 515.83 2002 2002 34.96 45.09 0.17 2002 42.16 3.02 35.02 190.88 256.52 2004 54.08 8.81 20.94 395.09 475.49 2002 54.08 8.8 20.94 395.13 475.49 2005 54.08 8.82 5.99 56 419.24 293.49 2002 54.07 8.81 6 410.11 370.48 2002 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula or use a macro?
Did you try it?
-- Don Guillett SalesAid Software "Randy" wrote in message ... Don, This works as well but my real data is several thousand rows and the AVERAGE statement is limited to 20. Randy "Don Guillett" wrote: This is an array formula that must be entered/edited using ctrl+shift+enter vs just enter =AVERAGE(IF((D2:D6=2002)*(E2:E60),E2:E6)) -- Don Guillett SalesAid Software "Randy" wrote in message ... I'm trying to come up with a formula that will sum the values in any of these columns based on the year column. For example, I have the following formula that sums the values in the Create column if the Year column = 2002. The second part of the formula needs to then count how many numbers were added so that an average can be calculated. That's the part I cannot figure out because I need to only count cells with a value 0 for whatever year I use in the formula. Can this be done via a formula or do I have to resort to a macro? I wouldn't be surprised if someone told me a totally different formula will get the result I need. Thanks! Randy =SUMIF($R2:$R6,"2002",M2:M6)/COUNTIF(M2:M6,"0") CREATE ESTIMATE APPROVE PENDING ACTIVE COMPLETE YEAR 284.69 2002 52.34 721.87 2003 52.31 79.73 263.14 2002 230.25 515.83 2002 2002 34.96 45.09 0.17 2002 42.16 3.02 35.02 190.88 256.52 2004 54.08 8.81 20.94 395.09 475.49 2002 54.08 8.8 20.94 395.13 475.49 2005 54.08 8.82 5.99 56 419.24 293.49 2002 54.07 8.81 6 410.11 370.48 2002 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula or use a macro?
I modified it include all 65536 and it works great!! I would appreciate it
if you could break the formula down and explain how it works. Randy "Don Guillett" wrote: Did you try it? -- Don Guillett SalesAid Software "Randy" wrote in message ... Don, This works as well but my real data is several thousand rows and the AVERAGE statement is limited to 20. Randy "Don Guillett" wrote: This is an array formula that must be entered/edited using ctrl+shift+enter vs just enter =AVERAGE(IF((D2:D6=2002)*(E2:E60),E2:E6)) -- Don Guillett SalesAid Software "Randy" wrote in message ... I'm trying to come up with a formula that will sum the values in any of these columns based on the year column. For example, I have the following formula that sums the values in the Create column if the Year column = 2002. The second part of the formula needs to then count how many numbers were added so that an average can be calculated. That's the part I cannot figure out because I need to only count cells with a value 0 for whatever year I use in the formula. Can this be done via a formula or do I have to resort to a macro? I wouldn't be surprised if someone told me a totally different formula will get the result I need. Thanks! Randy =SUMIF($R2:$R6,"2002",M2:M6)/COUNTIF(M2:M6,"0") CREATE ESTIMATE APPROVE PENDING ACTIVE COMPLETE YEAR 284.69 2002 52.34 721.87 2003 52.31 79.73 263.14 2002 230.25 515.83 2002 2002 34.96 45.09 0.17 2002 42.16 3.02 35.02 190.88 256.52 2004 54.08 8.81 20.94 395.09 475.49 2002 54.08 8.8 20.94 395.13 475.49 2005 54.08 8.82 5.99 56 419.24 293.49 2002 54.07 8.81 6 410.11 370.48 2002 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex If/Then formula? | Excel Discussion (Misc queries) | |||
complex formula | Excel Discussion (Misc queries) | |||
Complex formula | Excel Discussion (Misc queries) | |||
I Need Help with complex formula ? | Excel Worksheet Functions | |||
Complex Macro to perform an operation as it steps down cells? | Excel Worksheet Functions |