Excel 2002: How to compute average, min and max figures?

[Mr. Low]

Hello,

I need to compute the average , maximum and minimum price in Table 2 from
raw data in Table 1:

Table 1
A B C D
Model Price
1 L32 200
2 L32 250
3 L32 350
4 B64 150
5 B64 120
6 B64 110
7 B64 135
8 K32 980
9 K32 780
10 K32 880
11 K32 960
12 K32 910


Table 2
Average Max Min
20 L32 xxx xxx xxx
21 B64 xxx xxx xxx
22 K32 xxx xxx xxx


May I know if there is a way to input formulas at Cell B20,C20 and D20 and
copy down to get the answers ?

Thanks

Low

 

[Pete_UK]

You can use three array* formulae, as follows:

B20: =AVERAGE(IF($A$1:$A$12=$A20,$B$1:$B$12))
C20: =MAX(IF($A$1:$A$12=$A20,$B$1:$B$12))
D20: =MIN(IF($A$1:$A$12=$A20,$B$1:$B$12,10E10))

Then copy these down the next two rows.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE), rather than the usual <Enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. Use CSE
again if you subsequently edit/amend the formula.

Hope this helps.

Pete

 

[Dave Peterson]

Check your other post.

Hello,

I need to compute the average , maximum and minimum price in Table 2 from
raw data in Table 1:

Table 1
A B C D
Model Price
1 L32 200
2 L32 250
3 L32 350
4 B64 150
5 B64 120
6 B64 110
7 B64 135
8 K32 980
9 K32 780
10 K32 880
11 K32 960
12 K32 910


Table 2
Average Max Min
20 L32 xxx xxx xxx
21 B64 xxx xxx xxx
22 K32 xxx xxx xxx

May I know if there is a way to input formulas at Cell B20,C20 and D20 and
copy down to get the answers ?

Thanks

Low

 

[Mr. Low]

Hello Pete,

Thanks for your help.

Low