Search code examples
pythonexcelexcel-formulaformula

Formula to find if a set of values in excel column is out of range

enter image description here

There are certain values in a column in excel, like

108 108.7 2657 35678 4563 108.8 108.9 108.95

How in excel formula or python can we mark the cells with number out of range like in the above case 2657,35678 and 4563? Request inputs on it

This is the formula i am using:

=IF(ISBLANK(Final!A3),"Y",IF(ISNUMBER(Final!$A2:Final!$A4),IF(OR(AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3>Final!A2,Final!A3>Final!A4,Final!A3>Final!A5),AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),NOT(ISBLANK(Final!A5)),ISNUMBER(Final!A2:Final!A4),Final!A3<Final!A2,Final!A3>Final!A4,Final!A3<Final!A5),AND(NOT(ISBLANK(Final!A2)),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3<Final!A2,Final!A3<Final!A1,Final!A3<Final!A4),AND(ISBLANK(Final!A2),NOT(ISBLANK(Final!A4)),ISNUMBER(Final!A2:Final!A4),Final!A3>Final!A2,Final!A3<Final!A4,Final!A3<Final!A1)),"Y","."),IF(OR(AND(Final!A3>Final!A2,Final!A3>Final!A4),AND(Final!A3<Final!A2,Final!A3<Final!A4)),IF(AND(Final!$A3=Final!$A2,Final!$A3=Final!$A4),IF(Final!A3=MAX((Final!$A$2:Final!$A$100000=Final!$A2),Final!A2:INDEX(Final!A2:Final!A100000,SUMPRODUCT(--EXACT(Final!$A3,Final!$A2:Final!$A$100000)))),".","Y"),"."),".")))

The spikes need to be marked in a second sheet of Excel, if at all there is a high or low in the values in the excel.But if continous values are high or low, its not detecting the values.

I am also marking blank cells if any in between in the columns as 'Y' indicating a high or low in the values

The values in a column which fall out of range. Like if there is 1, 2,3, 6000,8000, 9000,4,5,6 here 6000, 8000 and 9000 are clearly out of range and do not fall between 1 and 6. I am able to identify if a single value in a column is out of range. But if continous values are out of range not able to identify it with excel formula.

Expected output in the second sheet of Excel with the formula applied:

enter image description here

Solution

  • This issue got solved using python SciPy library in Python using interpolation and regression model.