MIN function not working on result of calculated cell

I'm trying to use the MIN function to extract 3-4 lowest scores in a column. It works fine when values have been inserted without a formula. The cell I want to use MIN on is a number that is the result of a calculation. I've been searching to find what I need to add to my MIN formula to make this happen. See columns E and F in attached spreadsheet.

Thanks.

There are 2 zeroes in col C so small(..,1)=0 and small(..,2)=0 and small(..,3)=68 and so on. If you want to exclude the 0's type in this formula in col C

Please Login or Register  to view this content.

Try

=SMALL(C$7:C$18,1+COUNTIF(C$7:C$18,0))

This in E5 - =SMALL(A$5:A$18,ROW()-4)
This in F5 - =SMALL(C$5:C$18,ROW()-4+COUNTIF(C5:C18,"<1"))

Copy both down...

I appreciate replies from all -- rcm and Jonmo1, included. I used BadlySpelledBuoy's suggestion and it worked perfectly. Thank you, thank you, thank you! I don't understand the reason for "-4". Please explain.

In the formulas you had originally you had to change the last argument of the SMALL function each time from 1 to 2 to 3 to 4 as you looked for the 1st, 2nd, 3rd and 4th smallest numbers.

By using ROW()-X (where X is a number that fits in with your data (more about that in a minute)) you can just put the formula in once and drag down, without the need to individually amend each formula.

In the example workbook your data starts in row 5. So ROW()-4 in this row returns 1 (as per your original formula). When copied down to row 6 ROW()-4 returns 2, in row 7 it will return 3... and you can see where this is going I'm sure.

So if your data started in row 10 you would use ROW()-9 and copy down. If it started in row 1 you would just use ROW().

Perhaps a little overkill for looking up 4 values, but if you were looking up the smallest 100 values on a much larger dataset, you would appreciate this approach.

Does that help explain at all?

Originally Posted by BadlySpelledBuoy

By using ROW()-X (where X is a number that fits in with your data (more about that in a minute))

In the example workbook your data starts in row 5. So ROW()-4 in this row returns 1 (as per your original formula).

I like to use ROWS(A$1:A1) instead of ROW()-x
It incriments to A$1:A2 to A$1:A3 etc.. as it's dragged down.

This way it always begins with 1 regardless of the row you enter the formula on.
You don't have to 'think' about which row you're on and what x should be..

And it will still = 1 even if a row is inserted above the formula.

Originally Posted by Jonmo1

I like to use ROWS(A$1:A1) instead of ROW()-x
It incriments to A$1:A2 to A$1:A3 etc.. as it's dragged down.

This way it always begins with 1 regardless of the row you enter the formula on.
You don't have to 'think' about which row you're on and what x should be..

And it will still = 1 even if a row is inserted above the formula.

Yes, that is a much better approach, and the one I personally use. But I didn't want to make things more complicated than necessary

Thanks, again. I can't mark this as being solved yet. Will have to look over the comments and experiment when I have more time. Your solutions were good for my sample sheet (the one I attached), but I'm having a problem in my larger sheet. I can get back to this in a day or two. Am late for bridge!! Maybe I'll have better luck with that than this!