Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|