Count with several criteria and Average with complex criteria

Hi everyone!

I'm currently working on a worksheet, using Excel 2003 and I want to make it as much automated as it can (more time I spend now, less I spend on it later!).
I already spent mmuch time on it, but my excel skills are quite limited. When I see what you guys are able to do, it just amazes me!
Attached is the spreadsheet. It's quite big (and it's gonna get bigger), but there are several formulas I am looking for.

First, I want to calculate the average allocation (column G) by lead (column E).
I know how to count the different deals by leads, but I can't find a way to calculate the average. I think it's good to use the "participated" column (C) with "Yes" and "No". I've been using it for other formulas.
And as far as I've been looking into it, it looks like it's got to be a rather big formula. Any idea ?

My second question is how to calculate the average moves in prices (table from column Y to AE). But I want a specific average:
if the move in T+1 is positive (col Z), I want the average of T+2 (Col AA) for those stocks that were positive in T+1.
Something that would sound like: =AVERAGE([stock in T+2] IF[stock move in T+1]>0).
Let me know if that isn't clear (rather precise/complex question).

These two formulas would save me a lot of time and work (otherwise I'll have to make these calculation manually).
And I believe it's a rather good challenge for you guys !
Let me know if you have any good guesses.
Best,

Alash

in 2003, you will need to use a combination of Functions to get the Averages.

you can either use an array formula that looks like this:

=AVERAGE(IF(Z7:Z54>0,AA7:AA54))

which you need to confirm with CTRL+SHIFT+ENTER not just ENTER...

or you can use the SUMIF and COUNTIF functions like this:

=SUMIF(Z7:Z54,">0",AA7:AA54)/COUNTIF(Z7:Z54,">0")

which is confirmed only with ENTER

Does that help?

In the first question, you say you know how to count what you want...

Well you should be able to use the same AVERAGE(IF()) or SUMIF/COUNTIF formulas just adjusting the ranges.

If you are including multiple conditions you could use SUMPRODUCT/SUMPRODUCT instead of SUMIF/COUNTIF

eg

=SUMPRODUCT(--(A1:A10="X"),--(B1:B10="Y"),C1:C10)/SUMPRODUCT(--(A1:A10="X"),--(B1:B10="Y"))

where you are counting the number of Xs and Ys in the same rows and averaging column C

your added question:

=AVERAGE(IF(Z7:Z54>0,IF(AA7:AA54>0,AA7:AA54)))

or

=SUMPRODUCT(--(Z7:Z54>0),--(AA7:AA54>0),AA7:AA54)/SUMPRODUCT(--(Z7:Z54>0),--(AA7:AA54>0))

Great. Happy it worked.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save