Array formula for mid, max and avg?

[katiejo33]

I need some kind of magical formula to find the Min, Max & Avg (for each specific Acct # in one column) and place the result in a completely different worksheet

Example:

"Sheet 1"

Row_Col A ___Col E
1____Acct 25__26.79
2____Acct 25__56.23
3____Acct 25__37.84
4____Acct 25__10.15
5____Acct 52__10.45
6____Acct 52__29.75
7____Acct 52__11.79
8____Acct 52__28.74

So for this example, the results would be on "Sheet 2" and would show the Max for Acct 25 to be 56.23 (and the Avg would need to calculate all of the amounts in rows 1-4 since they are all the same Acct)

Then so on an so forth for each different Acct listed in Col A

The problem I am running into is that there are hundreds of Accts in Col A

HELP!!!

 

[Mackers]

Hi katiejo33

First off I would grab all of Col A (the acct numbers), copy it and paste it onto Sheet 2. Then click Data > Remove Duplicates. This should leave you with a list of all of the unique Accts in column A. If this list of Accts is on Sheet2 in column A starting from A2, your Accts on Sheet1 are in A1:A26 and the corresponding values are in B1:B26, the formulas would be as follows:

Average: =AVERAGEIF(Sheet1!$A$1:$A$26, Sheet2!A2, Sheet1!$B$1:$B$26)
Min (array formula, use Control+Shift+Enter): =MIN(IF(Sheet1!$A$1:$A$26=Sheet2!A2, Sheet1!$B$1:$B$26))
Max (array formula, use Control+Shift+Enter): =MAX(IF(Sheet1!$A$1:$A$26=Sheet2!A2, Sheet1!$B$1:$B$26))

 

[katiejo33]

Hi katiejo33

First off I would grab all of Col A (the acct numbers), copy it and paste it onto Sheet 2. Then click Data > Remove Duplicates. This should leave you with a list of all of the unique Accts in column A. If this list of Accts is on Sheet2 in column A starting from A2, your Accts on Sheet1 are in A1:A26 and the corresponding values are in B1:B26, the formulas would be as follows:

Average: =AVERAGEIF(Sheet1!$A$1:$A$26, Sheet2!A2, Sheet1!$B$1:$B$26)
Min (array formula, use Control+Shift+Enter): =MIN(IF(Sheet1!$A$1:$A$26=Sheet2!A2, Sheet1!$B$1:$B$26))
Max (array formula, use Control+Shift+Enter): =MAX(IF(Sheet1!$A$1:$A$26=Sheet2!A2, Sheet1!$B$1:$B$26))

Thank you for the quick response! The AVG def worked after some tweaking on my end, so hopefully the MIN and MAX will work as well.

THANK YOU SOOOO MUCH.

 

[Mackers]

No worries!

To clarify, in case it helps, the general formula for MIN() and MAX() should be as follows using control+shift+enter:

=MAX(IF(YourAcctsColumnA=AcctNumberRequired, CorrespondingValueColumnE))
=MIN(IF(YourAcctsColumnA=AcctNumberRequired, CorrespondingValueColumnE))

 

[katiejo33]

No worries!

To clarify, in case it helps, the general formula for MIN() and MAX() should be as follows using control+shift+enter:

=MAX(IF(YourAcctsColumnA=AcctNumberRequired, CorrespondingValueColumnE))
=MIN(IF(YourAcctsColumnA=AcctNumberRequired, CorrespondingValueColumnE))

Got it and it worked beautifully; thank you so so much. Wish I posted the question a lot earlier. I spent a good hour scouring the internet for a solution first.