Average while dropping the lowest number?

I'm pretty new to excel, and I have a problem.

I need a formula to average a set of numbers while dropping the lowest number, and only using the cells in the range that have numbers bigger than zero, but the minimum nuber that is dropped can be a zero.

I'll have eight numbers maximum. I'll get a new number each week, up to eight. I'd like to have the formula work at the end of each week, so after week one, it will use only one number, after week two, it will drop the lowest one and post that average, after week three, it will drop the lowest and average the remaining two, after week four, it will drop the lowest and average the remaining eight, and so on up to week eight.

This is for an archery league which is handicapped, and I don't want to do it on paper!! If anyone could shed some light, it would be much appreciated! T

Thanks.

I can use this formula once I have all eight scores, but it won't work until I get there. Is there a way to just use the numbers that I have so far?

=(SUM(range)-MIN(range)) / (count(range)-1))

I need it to just do the cells that have a number in them, but it has to be able to drop a zero, just not two or three zeros.

ok, the first thing I would do is to sort the column. Go to Data >sort. Lets say you have your data in column A. Have Cell B1 = Count(A:A),which will tell you how many weeks have passed. Then in
Cell B2 =AVERAGE(A1:INDIRECT("A"&B1)) This will give you the average, however it will not take off the lowest. All you have to do after that is put a -1 at the end of Cell B1 to get it to do taht for week 2 on, cant do it for week 1 cause it will then give you 0

It could have been easy if your data/scores were in one column
which was to be expected after your first question. But now I
read:

> My data is in:
> AA2,Y2,W2,U2,S2,Q2,O2,L2

If it's possible to put these scores in AB1 through AB8 then you could use
the formula of your second mail in AC1 through AC8.


Ruud

Why not
=SUM(AA2,Y2,W2,U2,S2,Q2,O2,L2)-MIN(AA2,Y2,W2,U2,S2,Q2,O2,L2)/COUNT(AA2,Y2,W2,U2,S2,Q2,O2,L2)

or
=IF(COUNT(AA2,Y2,W2,U2,S2,Q2,O2,L2)>1,SUM(AA2,Y2,W2,U2,S2,Q2,O2,L2)-MIN(AA2,Y2,W2,U2,S2,Q2,O2,L2)/COUNT(AA2,Y2,W2,U2,S2,Q2,O2,L2),"N/A")

Regards

Dav

That formula won't work for some reason. It comes up with a larger number than any one score, but it's not the pure sum either. I'm not sure what it's doing?

Sorry its me being slack, missing brackets

Why not
=(SUM(AA2,Y2,W2,U2,S2,Q2,O2,L2)-MIN(AA2,Y2,W2,U2,S2,Q2,O2,L2))/(COUNT(AA2,Y2,W2,U2,S2,Q2,O2,L2)-1)

or
=IF(COUNT(AA2,Y2,W2,U2,S2,Q2,O2,L2)>1,(SUM(AA2,Y2,W 2,U2,S2,Q2,O2,L2)-MIN(AA2,Y2,W2,U2,S2,Q2,O2,L2))/(COUNT(AA2,Y2,W2,U2,S2,Q2,O2,L2)-1),"N/A")

and the minus 1!

Hopefully that is correct

Regards

Dav

Now how do I make my range only numbers >0 inside of that formula? I think this will work, but I won't know until I get it to just look at the cells in my range that have numbers in them.

That second formula still wouldn't work for some reason.

Thanks, Jeremiah

On a side note, how do I copy a formula on down the rows without bringing the numbers from the cells that I copied with it. I'd like to copy the formula into each cell, but leave the cell blank unless there is an answer to the formula involved. Does that make sense? (I assume there is an easy answer for this one?)

I figured out how to do it. I set up a column for each week that goes past. So, you can look at the column that corresponds with the week you are on. It's a little crude, and not as streamlined as some of you pros would have it, but it works well.

Thanks for the help!! I'm glad I found this forum.

Jeremiah.