Min and max

[Brisbane Rob]

In column A are part numbers, running down to row 800. There are twelv
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 par
numbers in column C1 - C12, how can I put the minimum and maximum price
acheived for each part in columns D & E without sorting column B.
thought DMAX would work but it's either unsuitable or I'm using i
wrong.

Thanks for any idea

 

[Guest]

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

 

[Ardus Petus]

=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

 

[Brisbane Rob]

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