What to use
 

i have 7 ranges of
150-270,270-600,550-1000,1000-1600,1300-1900,550-2640,650-3120 Each range
comes with a price of cost and manhours. I need a formula so that when I
enter a number it will select for me the correct range and then give me the
cost and labor price of that range. For a smaller selection I would have
used if but with 7 it doesn't work. Any help would be appreciated
Thanks

What to use
 

I'm confused

Which range does 551 fall in
270-600
550-1000
There are several instances of this


Mike
"Excluxe" wrote:

i have 7 ranges of
150-270,270-600,550-1000,1000-1600,1300-1900,550-2640,650-3120 Each range
comes with a price of cost and manhours. I need a formula so that when I
enter a number it will select for me the correct range and then give me the
cost and labor price of that range. For a smaller selection I would have
used if but with 7 it doesn't work. Any help would be appreciated
Thanks

What to use
 

I realize several of these ranges lap over each other. but that is how they
are. I plan though on just changing the ranges so that don't overlap. But I
didn't change them when I posted them just to see if there is away around
that without changing them. Here are the ajusted ranges.
270-575,575-1000,1000-1450,1450-1900,1900-2640,2640-3120

"Mike H" wrote:

I'm confused

Which range does 551 fall in
270-600
550-1000
There are several instances of this


Mike
"Excluxe" wrote:

i have 7 ranges of
150-270,270-600,550-1000,1000-1600,1300-1900,550-2640,650-3120 Each range
comes with a price of cost and manhours. I need a formula so that when I
enter a number it will select for me the correct range and then give me the
cost and labor price of that range. For a smaller selection I would have
used if but with 7 it doesn't work. Any help would be appreciated
Thanks

What to use
 

Set up a little table somewhere (eg X1 to Z8 on the same worksheet)
and lay it out like this:

0 cost rate
270 cost rate
575 cost rate
1000 cost rate
1450 cost rate
1900 cost rate
2640 cost rate
3120 cost rate

Then, assuming you put your hours in column A starting with A2, this
formula will return the cost:

=VLOOKUP(A2,X$1:Z$8,2)

and this will return the hourly rate:

=VLOOKUP(A2,X$1:Z$8,3)

Hope this helps.

Pete

On Jul 13, 5:30 pm, Excluxe wrote:
I realize several of these ranges lap over each other. but that is how they
are. I plan though on just changing the ranges so that don't overlap. But I
didn't change them when I posted them just to see if there is away around
that without changing them. Here are the ajusted ranges.
270-575,575-1000,1000-1450,1450-1900,1900-2640,2640-3120



"Mike H" wrote:
I'm confused

Which range does 551 fall in
270-600
550-1000
There are several instances of this

Mike
"Excluxe" wrote:

i have 7 ranges of
150-270,270-600,550-1000,1000-1600,1300-1900,550-2640,650-3120 Each range
comes with a price of cost and manhours. I need a formula so that when I
enter a number it will select for me the correct range and then give me the
cost and labor price of that range. For a smaller selection I would have
used if but with 7 it doesn't work. Any help would be appreciated
Thanks- Hide quoted text -

- Show quoted text -