Array, array, my kingdom for array

Well maybe not my kingdom, but certainly my heartfelt thanks.

I am only new to arrays, sumproduct etc and at the moment am really pushing buffalo chips uphill so any help is appreciated.

I want to extract from a list of Salesmen/Sales (for example) as such :

Salesman Sale

John $25
John $35
John $20
Bill $40
Greg $60
Greg $40
Greg $30
Greg $50

a summary list :

John $30
Greg $55

Where the average of the top two sales (only) is shown for each salesman and a salesman with less than two sales is not included on the second list (or alternatively is shown but with a null in the average column)

Thanks in advance for any ideas.

Barry

Thanks a lot, Duane. Works beautifully, even with my very large list.

Barry

Just as a tutorial, here is an alternative that uses a 2-dimensional array {1,2}.
Hope it can be of interest.

Ola Sandström

Thanks Olasa. I have added it to my examples folder. In time I will go back and find more elegant solutions to a lot of previous multi step calculations in my worksheets.

Regards,
Barry

Could someone please now adjust or rework the above formula/s to include another condition such that the task now is to find the average of the top (most $) two sales from only the last three sales - rather than from all sales.

All sales on my list have been sorted in date descending order so in my example list above Greg's fourth listed sale of $50 now becomes ineligible for inclusion in the average calculation.

The resultant average list would now become :

John $30
Bill $0
Greg $50

Thanks again for any help.

Barry

Thanks again Duane. You have just trimmed another couple of intermediate steps from my calculations.

Barry

The formulas provided by duane can be shortened as follows...

For the Top 2 sales:

=IF(COUNTIF($A$1:$A$8,A10)>1,AVERAGE(LARGE(IF($A$1:$A$8=A10,$B$1:$B$8),{1,2})),0)

...confirmed with CONTROL+SHIFT+ENTER.

For the Top 2 sales from the last 3 sales:

=IF(COUNTIF($A$2:$A$9,$A11)>1,AVERAGE(LARGE(IF(($A$2:$A$9=$A11)*($C$2:$C$9<4),$B$2:$B$9),{1,2})),0)

...confirmed with CONTROL+SHIFT+ENTER.

Also, is it possible for your data to be in the following sequence...

Please Login or Register  to view this content.

If so, you may need to change the formula for Column C to the following...

=COUNTIF($A$2:A2,A2)

Otherwise, Bill's average would include sales of $75 and $65, with the latter not being part of the last 3 sales.

Hope this heps!

Several more questions if I may. I have been experimenting with different arrays and was wondering if someone could explain what is happening in the following example.

=IF(COUNTIF($A$2:$A$9,A11)>1,SUM(($A$2:$A$9=A11)*($B$2:$B$9)*($C$2:$C$9<4)),0)

correctly sums the last three sales for each salesman with more than one sale but if I substitute AVERAGE for SUM in the array the answer is the sum of the relevant sales divided by count of the total cells in the range, not count of the individual salesman's sales?

Also, being totally new to arrays/sumproduct I have been trying to use Tools>Formula>Auditing>Evaluate Formula to gain an insight into what is going on in these arrays but Excel crashes each time I get a couple of steps into the evaluation. (XP Pro and Excel 2003) Is this a common problem or should I be trying a different approach?

Thanks again for all the help. This forum is definitely one of the best resources on the www.

Barry

That's because you'll need to use the IF function with AVERAGE. The IF function returns a FALSE value when a condition is not met, which AVERAGE ignores. So, therefore, if we take a look at the following formula...

=AVERAGE(IF(($A$2:$A$9=A11)*($C$2:$C$9<4),$B$2:$B$9))

...the following array is returned prior to averaging (based on your original data)...

25
35
20
FALSE
FALSE
FALSE
FALSE
FALSE

...which results in 26.66666667. Without the IF function, the array returned prior to averaging would be...

25
35
20
0
0
0
0
0

...which results in 10.

Hope this helps!