Counting rows that fit within a range

  • Thread starter Thread starter Scott Lyon
  • Start date Start date
S

Scott Lyon

I've got what seems to be a simple problem to do by hand (if it weren't for
the fact that the table in question isn't exactly small). So I was hoping I
could do it (somehow) in an Excel spreadsheet.


What I have, is a column of numbers (to one decimal point), assumed to be
sorted in descending order.

I want to have another column (calculated by Excel) that will count how many
rows have a larger value (in the first column) than it, but only up to a
certain range (by adding a constant to the value of that row).


For example, here's some data, with the first column supplied by my data,
and the second column ideally generated by Excel:

For this example, the constant is 7

54.2 N/A (because there is no value above this - note: I don't care
if this shows up as 0 or #VALUE!)
54.2 1 (because 54.2 + 7 = 61.2, and 1 row is between
(all-inclusive) 54.2 and 61.2)
45 0 (because 45 + 7 = 52, and 0 rows are between 45 and 52)
43.3 1 (because 43.3 + 7 = 50.3, and 1 row is between 43.3 and
50.3)
41.5 2 (because 41.5 + 7 = 48.5, and 2 rows is between 41.5 and
48.5)
35.1 1 (because 35.1 + 7 = 42.1, and 1 row is between 35.1 and
42.1)


Is there an easy way to do this in Excel?


Thanks!
-Scott
 
Hi Scott,

maybe this firmula will help
=ROW()-MATCH(A1+7,$A$1:$A$20,-1)-1

Some comments:
- I did not understood your second exmaple row as there is no value
=61.2. So the above formula will also result in a "#NA"
Click to expand...
- You have to change the range according to your need
- If you want to surpress the "#NA" errors use a combination of IF and
ISNA

Frank
 
Back
Top