Min function

[drewdt3]

Hi, could anyone tell me how to remove the 2 lowest numbers from a row or colum of numbers. I know I can use MIN to remove the lowest but need to remove the 2 lowest.
Thanks

 

[Brianwarnock]

MIN just returns the number to you it doesn't remove it from the row/column.

I think that you will need to write code.

Brian

 

[boblarson]

Never mind - I read it wrong.

 

[qafself]

If you just want to remove them from calculations use SMALL

 

[Brianwarnock]

Like Min , Small just returns the value.

The OP dose not say what he wants to do, I assume delete the value and then what, close up the range. What if the range contains say 3 1s, or 1,2 , 2

is it a recurring thing or a one off, how big is the range, is it simpler to do it manually.

Brian

 

[drewdt3]

I need to collate a league table of up to 50 people with 10 rounds of which the top 8 for each person will count. I need to be able to withdraw from the running total the bottom 2 results for each person on a monthly basis.

 

[qafself]

Provided each number only occurs once, if your results were in A1:A10

Sum(A1:a10)-(small(a1:a10,1)+small(A1:a10,2))

would give you a total of the 8 highest scores.

The problem comes if there is a repitition of any low score as repeated instances will all be subtracted.


Ed

 

[Brianwarnock]

Provided each number only occurs once, if your results were in A1:A10

The problem comes if there is a repitition of any low score as repeated instances will all be subtracted.


Ed

No it doesn't, if the guy had scored 10 twos the formula would correctly return 16

Brian

 

[qafself]

Brian,

As usual you are right! My testing was at fault - Mea Culpa.

Ed

 

[Brianwarnock]

I had the advantage of being here before, that's why I kept asking for clarification of what he wanted.

Brian

 

[drewdt3]

Thanks guys I knew someone would come up with the answer.

Cheers