英文:
How to check whether the current value of a cell is within a certain value of all previous cells
问题
I wanted to inquire if there was a formula that could check whether the current cell is within 12 (above or below) all previous cells and exclude if this is true. For example
在这里，我想询问是否有一个公式，可以检查当前单元格是否比所有之前的单元格小于或大于12，如果是，则排除。
In the picture above the 3rd row (value of 4874) is less than 12 away from the 1st row value (4875) so is excluded.
在上面的图片中，第3行（值为4874）与第1行的值（4875）之间的差值小于12，因此被排除。
All formulas i have seen so far seem to utilise max or min functions but im not sure if that is applicable in this case.
到目前为止，我看到的所有公式似乎都使用了max或min函数，但我不确定是否适用于这种情况。
Any help would be appreciated. Thanks!
非常感谢任何帮助！
英文:
I wanted to inquire if there was a formula that could check whether the current cell is within 12 (above or below) all previous cells and exclude if this is true. For example
In the picture above the 3rd row (value of 4874) is less than 12 away from the 1st row value (4875) so is excluded.
All formulas i have seen so far seem to utilise max or min functions but im not sure if that is applicable in this case.
Any help would be appreciated. Thanks!
答案1
得分: 3
=IF(COUNTIFS(A$2:A2,"<=A3",A$2:A2,">="&A3-12)+
COUNTIFS(A$2:A2,">="&A3,A$2:A2,"<="&A3+12),"排除","包括")
英文:
Countifs is available in Excel-2010 so you can use this starting in A3:
=IF(COUNTIFS(A$2:A2,"<=A3",A$2:A2,">="&A3-12)+
COUNTIFS(A$2:A2,">="&A3,A$2:A2,"<="&A3+12),"Exclude","Include")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论