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

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

Complex formula or use a macro?
 

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

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

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

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.

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

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

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