What to use

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
I'm confused

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


Mike
 
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
 
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
 
Back
Top