根据间隔选择列表中的值？ [英] Selecting values in a list based on an interval?

问题描述

我有一大列值表示海拔高度（米）。这是一个例子：

  177.5 
 178.0 
 180.1 
 181.9 
 182.4 
 182.5 
 184.0 
 185.3 
  

我想创建公式一个不同的列，每2.5m间隔给出1，间隔后的第一个值。以下是根据2.5间隔搜索的号码：

  177.5 
 180.0 
 182.5 
 185.0 
  

由于我的原始列表中没有确切的数字，希望公式从列表中的以下数字旁边的相应列中放置1：

  177.5 
 180.1 
 182.5 
 185.3 
  

我要找什么样的公式？我不太确定什么叫这种问题，否则我会在stackoverflow中找到更简单的时间。

解决方案

p>像下面的图像可能会起作用。注意，B2填充中的公式是：
= IF（A3> = SUM（$ B $ 2：B2）* $ C $ 1 + $ E $ 1,1,0）

• 需要排序A，


• 需要C1包含间隔搜索


• 需要B包含1表示起始值


• 使用$表示固定位置，因为总和的范围移动它必须是动态的其余的值是静态的。


• 不处理void。说如果你从185跳到200，它将标记200，甚至199，因为199是187.5之后的下一个最接近的数字，这将是190

I have a large column of values representing elevation in metres. Here is an example:

177.5
178.0
180.1
181.9
182.4
182.5
184.0
185.3

I want to create formula in a different column that gives a 1 for every 2.5m interval or the first value after the interval. Here are the the numbers I would be searching for based on the 2.5 interval:

177.5
180.0
182.5
185.0

Since I don't have those exact numbers in my original list, I want the formula to place a 1 in the corresponding column next to the following numbers from the list:

177.5
180.1
182.5
185.3

What kind of formula would I be looking for? I'm not too sure what to call this kind of problem otherwise I'd have an easier time searching for it on stackoverflow.

解决方案

Something like the below image may work. Note the formula in B2 filling down is: =IF(A3>=SUM($B$2:B2)*$C$1+$E$1,1,0)

• Requires that A be sorted,
• Requires that C1 contain the interval searching for
• Requires that B contain a 1 indicating the starting value
• uses $ to denote fixed postion since the range on the sum moves it had to be dynamic the rest of the values were static.
• Doesn't handle voids. Say if you skipped from 185 to 200. it will flag 200, and even 199 because 199 is the next closest number to the interval after 187.5 which would have been 190

这篇关于根据间隔选择列表中的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！