Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to compute average, min and max figures?
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 -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to compute average, min and max figures?
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 On Aug 19, 3:18*pm, Mr. Low wrote: 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 -- A36B58K641 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to compute average, min and max figures?
Check your other post.
Mr. Low wrote: 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 -- A36B58K641 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to compute average, min and max figures?
Hello Pete,
Thanks for your help. Low -- A36B58K641 "Pete_UK" wrote: 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 On Aug 19, 3:18 pm, Mr. Low wrote: 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 -- A36B58K641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: Max, Min and Average formulas | Excel Discussion (Misc queries) | |||
Excel 2002 AVERAGE Function | Excel Discussion (Misc queries) | |||
Conditional Statement to Compute Average | Excel Worksheet Functions | |||
How do i compute the average value of 100 iterations? | Excel Worksheet Functions | |||
average function in Excel 2002 | New Users to Excel |