Remove blanks from array, into new array, without helper column

[caseyAlex]

Good afternoon,

I have a excel database which is daily enteries of the amount of oil consumption from a machine.
These enteries can also be blank if that entry was just a check, and those values are required to be ignored during the following steps.

I would like each row to check the oil consumed of the 4 rows above it, which are not blank, and detemine if they are ALL above a preset value. Lets say that value is 5

The column would look like

Date: Consumed High Consecutive
-------- --------- ---------------
01JAN19 5.9 False
02JAN19 2 False
03JAN19 0 False
04JAN19 5.6 False
05JAN19 5.3 False
06JAN19 0 False
07JAN19 5.8 False
08JAN19 5.9 True


07JAN19 has 4 days consecutive about 5, as the 0 day is not counted. Note, days are not related to date, but operations.


I made an forumla that reads each row and 4 above, to check for values above 5 and that worked until a 0 entry was added as it reads that 0 entry as one of the operations.

I created an array forumla which reads the current row and 8 (maximum, assuming there is a few 0 values) above for all values that aren't 0 and it returned the following array


Formula {=IF(A1:A8>5, A1:A8, "")}
Return "5.9", "2", "", "5.6", "5.3", "", "5.8", "5.9"


From this i was hoping to remove the blanks to leave me with an array of

"5.9", "2", "5.6", "5.3", "5.8", "5.9"

and then read the last 4 values, being 5.6, 5.3, 5.8 and 5.9 and determine if they are above 5 and return a boolean variable.


I have no idea how to manage this, so any help would be super appreicated.
If this way of tacking the problem is not the best then can you please assist me with that.

Basically and i need is for each day to check the itself and 3 operations above it (which aren't 0) and detemine if they are above 5.

Apologies in advance for any confusion

Thanks

Casey
****** id="cke_pastebin" style="position: absolute; top: 280px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">07JAN19 5.8 True</body>

 

[caseyAlex]

Formatting correction

Date: Consumed High Consecutive
-------- --------- ---------------
01JAN19 5.9 False
02JAN19 2 False
03JAN19 0 False
04JAN19 5.6 False
05JAN19 5.3 False
06JAN19 0 False
07JAN19 5.8 False
08JAN19 5.9 True

 

[jtakw]

Hi,

I am unclear on your requirements ( or your explanation needs elaboration )…

So, if this is an "ongoing" check, does it "reset" every time there's a value above 0 but 5 or less, or does it continue to show TRUE once there's 4 or more values above 5 ?

I think maybe, show more lines of data and expected results would be helpful.