Flag the smallest number in a recurring set of 6 numbers in a long column in the adjacent

Hi everyone,

I wonder if anyone can help with hopefully a simple little task I'm trying to do in Excel 2010.

I have a long single column of thousands of numbers, and I just want to put a "1" flag in the next adjacent column to indicate which is the smallest number from every group of 6 numbers as you work down the column.

For instance - in the column below there is a "1" next to the smallest number of the 1st 6 numbers which is 4.5, and another 1 next to the smallest number of the next 6 numbers going down, which is 1.8, and so on for several thousand additional instances.

Column A Column B
9.8
4.5 1
11.1
9.5
8.2
7.9
2.1
1.8 1
5.2
4.1
3.7
8.1

I'd really appreciate a help with the necessary formula to achieve what I hope is a relatively simple task.

Thanks in anticipation,

Malcolm.

Hi again,

Thanks for the formula.

I've tested it out on a small column of numbers (96 in total).

It works most of the time, but there are a few instances where it makes a mistake - which is quite baffling.

Is there any chance you could take a quick look and see if you can see why please ?

I have attached a small spreadsheet where I have implemented the formula as instructed.

These are the errors that I think should not be happening -


Cell A20 - 3.85 is correctly flagged with a 1 in cell B20
Cell A24 - 6 is incorrectly flagged with a 1 in cell B24 - within the same group of 6 numbers

Cell A74 - 3.45 is correctly flagged with a 1 in cell B74
Cell A78 - 4 is incorrectly flagged with a 1 in cell B78

Thanks,

Malcolm.

Hi,

Thanks very much. This now works perfectly.

Much appreciated.

Hi again,

Sorry everyone - I spoke too soon ! This formula still appears to have a problem.

I tested it out on a single column of approx. 10,000 numbers, and I noticed that it's immediately run into problems.

Now it's becoming extremely baffling, because I tested it on a small sample of numbers yesterday, and it worked OK.

I've attached a spreadsheet with two tabs - 1 for the numbers that it worked OK with, labelled 'working example', and another labelled 'not working example' that shows complete groups of 6 numbers that there is no flag for whatsoever.

Each worksheet is just a simple column of numbers in column A, and the formula to flag the minimum number for each successive group of six numbers working down the column, with a '1' in the adjacent cell in column B.

That's it..

I wonder if anyone can crack this stubborn little nut !!

I'd really appreciate a hand with this, as I cannot figure out why it's not working.

Thanks everyone.

Gosh - I think it's now working correctly - I can't seem to find any glitches, although it is rather a job checking over 10000 numbers.

A very sincere thank you Icestationzbra for your kind help in solving this problem.

Kind regards,

Malcolm.