Index by Range
 

I have a range of vehicles splited by mileage (a row with 10 categories) and
in another sheet I have other categories that I have to sum the vehicles of
the nearest higher mileage range. Ex

Model 10.000 20.000 30.000 40.000 50.000 60.000
.........
X 2 3 5 3
8 9
Y 4 2 3 2
7 4
Z 5 7 2 1
6 5

and I need to know the sum vehicles of around and higher 44.000 KM which
in this specific case is inside the 50.000km range and should return 21(sum
of vehicles inside this category)
I can not use if conditions cause I ´m limited to 7 whereas I have 10
categories.
Is there anybody who can help me?

Index by Range
 

Here's one crack at this ..

Assume table as posted is in Sheet1 within cols A to G, data in row2 down,
"km category" headers* running in B1 across: 10000, 20000, ... , models
listed in A2 down to say, A100
*headers are assumed real numbers and in ascending sequence across

In another Sheet2,

Assume the km will be input in A2 down, eg:

44000
39000
55000
....

Put in B2:
=IF(A2="","",SUM(OFFSET(Sheet1!$A$2:$A$100,,MATCH( A2,Sheet1!$B$1:$IV$1,1)+1)))
Copy down

We'd get the required results:

44000 21
39000 6
55000 18
....

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BC" wrote:
I have a range of vehicles splited by mileage (a row with 10 categories) and
in another sheet I have other categories that I have to sum the vehicles of
the nearest higher mileage range. Ex

Model 10.000 20.000 30.000 40.000 50.000 60.000
.........
X 2 3 5 3
8 9
Y 4 2 3 2
7 4
Z 5 7 2 1
6 5

and I need to know the sum vehicles of around and higher 44.000 KM which
in this specific case is inside the 50.000km range and should return 21(sum
of vehicles inside this category)
I can not use if conditions cause I ´m limited to 7 whereas I have 10
categories.
Is there anybody who can help me?

Index by Range
 

A sample construct is available at:
http://www.savefile.com/files/7587118
Index by range.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Index by Range
 

........MATCH(A2,Sheet1!$B$1:$IV$1,1)+1)))

What happens when the lookup value is 60000?

Biff

"Max" wrote in message
...
Here's one crack at this ..

Assume table as posted is in Sheet1 within cols A to G, data in row2 down,
"km category" headers* running in B1 across: 10000, 20000, ... , models
listed in A2 down to say, A100
*headers are assumed real numbers and in ascending sequence across

In another Sheet2,

Assume the km will be input in A2 down, eg:

44000
39000
55000
...

Put in B2:
=IF(A2="","",SUM(OFFSET(Sheet1!$A$2:$A$100,,MATCH( A2,Sheet1!$B$1:$IV$1,1)+1)))
Copy down

We'd get the required results:

44000 21
39000 6
55000 18
...

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BC" wrote:
I have a range of vehicles splited by mileage (a row with 10 categories)
and
in another sheet I have other categories that I have to sum the vehicles
of
the nearest higher mileage range. Ex

Model 10.000 20.000 30.000 40.000 50.000
60.000
.........
X 2 3 5 3
8 9
Y 4 2 3 2
7 4
Z 5 7 2 1
6 5

and I need to know the sum vehicles of around and higher 44.000 KM which
in this specific case is inside the 50.000km range and should return
21(sum
of vehicles inside this category)
I can not use if conditions cause I ´m limited to 7 whereas I have 10
categories.
Is there anybody who can help me?

Index by Range
 

"Biff" wrote:
........MATCH(A2,Sheet1!$B$1:$IV$1,1)+1)))
What happens when the lookup value is 60000?

Good point on the upper bound. My error. Thanks.

Put instead in B2, copy down:
=IF(A2="","",IF(A2=MAX(Sheet1!$B$1:$IV$1),SUM(OFF SET(Sheet1!$A$2:$A$100,,MATCH(A2,Sheet1!$B$1:$IV$1 ,1))),SUM(OFFSET(Sheet1!$A$2:$A$100,,MATCH(A2,Shee t1!$B$1:$IV$1,1)+1))))

Earlier sample construct revised (at same link)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---