max value and average

[fx1]

Hi,

I have a list of parts along with prices some parts repeat (different PO).
I need a formula that calculates average and max for each part.
I have 2 worksheets already with unique part id`s one for average and one for max value.

I used if statement but something is not working.

Please help

Thnx

 

[lozzablake]

post the formula that is not working

 

[fx1]

what I`m trying to use is

IF(MAX(LIST=CELL-from the unique list,Price range))

it gives me the max value from the whole list not for the particular part id.

I~m basing it on the SUMIF fromula

Thnx

 

[fx1]

for now I`m gonna use the SUBTOTALS
but the problem I`m facing is not being able to remove the `Total` on some part when using Replace option

but I still would like somebody to let me know the formula for future reference and knowledge.

 

[Scott Huish]

Try this:

Average formula:
=SUMIF(A1:A100,"Part1",B1:B100)/MAX(COUNTIF(A1:A100,"Part1"),1)

MAX formula:
=MAX(IF(A1:A100="Part1",B1:B100))

The MAX formula must be confirmed with CTRL-SHIFT-ENTER