Complex formula or use a macro?

[Guest]

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

 

[Guest]

Try:

=SUMPRODUCT(--(G2:G12=2002),--(A2:A12>0),(A2:A12))/SUMPRODUCT(--(G2:G12=2002),--(A2:A12>0))

 

[Don Guillett]

This is an array formula that must be entered/edited using ctrl+shift+enter
vs just enter
=AVERAGE(IF((D26=2002)*(E2:E6>0),E2:E6))

 

[Guest]

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!

 

[Guest]

Don,

This works as well but my real data is several thousand rows and the AVERAGE
statement is limited to 20.

Randy

 

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Guest]

Randy,
AVERAGE is NOT limited to 20 rows but 20 "arguments" so Don's
formula is perfectly usable.

 

[Don Guillett]

Did you try it?

 

[Guest]

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