Advanced Aggregate function? - Find average without zeros and without lowest value

[yrcowboyge]

I am trying to find a formula that gets me the average number(Dollars) for the entire column but it needs to ignore the zeros/blank along with the lowest number. I thought this would be an aggregate function - another aggregate function but cannot get it to work. So in the example below I need it to ignore the zeros along with the -$5412. Greatly appreciate any help.

example column A:
$630
$(212)
$205
$502
$(5412)
$0
$0
$0

[bebo021999]

It assums data in A1:A8

Try:

Please Login or Register  to view this content.

[hbgurley]

=(sum(a1:a8)-min(a1:a8))/(countif(a1:a8,"<>0")-1)

[bebo021999]

=(sum(a1:a8)-min(a1:a8))/(countif(a1:a8,"<>0")-1)

Assume column A is a list of "zero" and positive numbers
(sum(a1:a8)-min(a1:a8)) sum all values that greater than 0, including smalles valuest that greater than 0
i,e

630
2
205
502
1
0
0
0

returns 335, while it should be 334.75

[FDibbins]

How is this fundamentally different from your other thread here?
https://www.excelforum.com/excel-for...est-value.html

Seems to me you are asking the same thing, just changing the data