CODEWITHSUNDEEP
excelexcel-formula
JC11
JC11

Reputation: 473

Selecting values in a list based on an interval?

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.

Upvotes: 2

Views: 4040

Answers (1)

xQbert
xQbert

Reputation: 35353

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

enter image description here

Upvotes: 3

Related Questions