# Array formula for mid, max and avg?

#### katiejo33

##### New Member
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!!!

Last edited:

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Mackers

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### New Member
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.

Replies
6
Views
240
Replies
3
Views
164
Replies
14
Views
769
Replies
36
Views
688
Replies
2
Views
143

1,195,712
Messages
6,011,264
Members
441,598
Latest member
chrispaulpearce

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back