Solved

# Max, Min, Average (excel 2003)

Posted on 2011-03-22
378 Views
Please see attached.  Have column A with Product Type and Column B with Cost.  Need to obtain max, min, average per product.  Need to present it in cells E5:G6.

Thanks,
JP Max-min-avg.xls
0
Question by:easycapital
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2

LVL 33

Accepted Solution

jppinto earned 250 total points
ID: 35189418
Here you go...please check the attached solution...

jppinto
Max-min-avg-1-.xls
0

LVL 33

Expert Comment

ID: 35189429
Please note that I've used array formulas that need to be entered by using Ctrl+Shift+Enter (the brackets should appear after the enter).
0

LVL 50

Assisted Solution

barry houdini earned 250 total points
ID: 35189447
Hello JP,

You can use "array formulas" for this, e.g. in E5 use this formula

=MAX(IF(\$A\$3:\$A\$9=\$D5,\$B\$3:\$B\$9))

confirmed with CTRL+SHIFT+ENTER and copied to E6

Then identical in F5:G6 but with MIN/AVERAGE instead of MAX

see attached

regards, barry
26903135.xls
0

LVL 50

Expert Comment

ID: 35189455
0

LVL 33

Expert Comment

ID: 35189459
No problem Barry, it happens to all of us... :)
0

Author Closing Comment

ID: 35191038
Excellent!
One comment though. When I used the "IF" formula and used 0 for the false, the results were not always right.  Any thoughts.
Thanks,
JP
0

Author Comment

ID: 35191049
So I used the formula just as indicated without the zero, but I just thought it was good to point out the results.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month3 days, 8 hours left to enroll