Min and max
 

In column A are part numbers, running down to row 800. There are twelve
different parts numbers. Column B has the price acheived for each sale.
The same part can be sold at different prices. If I list the twelve part
numbers in column C1 - C12, how can I put the minimum and maximum prices
acheived for each part in columns D & E without sorting column B. I
thought DMAX would work but it's either unsuitable or I'm using it
wrong.

Thanks for any ideas


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=550293

Min and max
 

Hi Rob,

very simple way is to use Pivot table. You just select all the data
(a1:b800), create pivot (Data - PivotTable...) with wizard and then put value
(B) as first field (between row and column field and part (A) as row field.
Then just right click on field name with with name of the B column (the name
is default "Sum of <name_of_the_B_Column"), choose "Field settings" and
select "Min" or "Max" under "Summarize by". The field name will change to
"Min of <name_of_the_B_Column" or "Max of <name_of_the_B_Column".

Regards,
Nika Lampe

"Brisbane Rob" wrote:


In column A are part numbers, running down to row 800. There are twelve
different parts numbers. Column B has the price acheived for each sale.
The same part can be sold at different prices. If I list the twelve part
numbers in column C1 - C12, how can I put the minimum and maximum prices
acheived for each part in columns D & E without sorting column B. I
thought DMAX would work but it's either unsuitable or I'm using it
wrong.

Thanks for any ideas


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=550293

Min and max
 

=MIN(IF($A$1:$A$800=$C1,$B$1:$B$800))
=MAX(IF($A$1:$A$800=$C1,$B$1:$B$800))

Array formulae to validate with Ctrl+Shift+Enter


HTH
--
AP

"Brisbane Rob" a
écrit dans le message de news:
...

In column A are part numbers, running down to row 800. There are twelve
different parts numbers. Column B has the price acheived for each sale.
The same part can be sold at different prices. If I list the twelve part
numbers in column C1 - C12, how can I put the minimum and maximum prices
acheived for each part in columns D & E without sorting column B. I
thought DMAX would work but it's either unsuitable or I'm using it
wrong.

Thanks for any ideas


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile:
http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=550293

Min and max
 

Once again, thanks guys. Both work but I'll be going with the array
formula.


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=550293