Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return a certain value if a number is within a range
I would like help in working out the following formula.
I have a number that is generated from the amount of defects for the month, which would be between 0-100. I would like to generate a new number from this that would be between 1-10. For example, if the total defects were between 20-30, i would like to see a 6 in the next colum, if it was between 80-100 I would like to see a 10 etc Any ideas on how to write this formula? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return a certain value if a number is within a range
What about defects between 0-20 and 30-80?
-- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "Caconz" wrote in message ... I would like help in working out the following formula. I have a number that is generated from the amount of defects for the month, which would be between 0-100. I would like to generate a new number from this that would be between 1-10. For example, if the total defects were between 20-30, i would like to see a 6 in the next colum, if it was between 80-100 I would like to see a 10 etc Any ideas on how to write this formula? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return a certain value if a number is within a range
"Peo Sjoblom" wrote:
What about defects between 0-20 and 30-80? Portland, Oregon Well, yes, they would all have to have results as well, but I was just looking for a start on the formula. The total accurate list would be 0-5 = 1, 5-10 = 2, 10-15 = 3, 15-20 = 4, 20-25 = 5, 25-30 = 6, 30-50 = 7, 50-70 = 8, 70-85 = 9, 85-100 = 10 Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return a certain value if a number is within a range
A simple VLOOKUP should do the trick
Col A Col B 0 1 5 2 10 3 etc. 85 10 100 -1 (-1 to signify error) =VLOOKUP(A1,$B$1:$B$B11,2) George Caconz wrote: "Peo Sjoblom" wrote: What about defects between 0-20 and 30-80? Portland, Oregon Well, yes, they would all have to have results as well, but I was just looking for a start on the formula. The total accurate list would be 0-5 = 1, 5-10 = 2, 10-15 = 3, 15-20 = 4, 20-25 = 5, 25-30 = 6, 30-50 = 7, 50-70 = 8, 70-85 = 9, 85-100 = 10 Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return a certain value if a number is within a range
"George" wrote: A simple VLOOKUP should do the trick Col A Col B 0 1 5 2 10 3 etc. 85 10 100 -1 (-1 to signify error) =VLOOKUP(A1,$B$1:$B$B11,2) George Thanks for the reply, but that would be a little cumbersome, as for every number from 0 to 100 I would have to place in a table with its corrosponding result. I will use this, unless someone else has a better option. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return a certain value if a number is within a range
No, the table would only be 11 rows
The way vlookup works, anything between 0 to less than 5 would take the zero, etc up to anything between 85 to 100 gets 10. Anything over 100 would get -1 (just in case) It fits your number in the ranges between each row George Caconz wrote: "George" wrote: A simple VLOOKUP should do the trick Col A Col B 0 1 5 2 10 3 etc. 85 10 100 -1 (-1 to signify error) =VLOOKUP(A1,$B$1:$B$B11,2) George Thanks for the reply, but that would be a little cumbersome, as for every number from 0 to 100 I would have to place in a table with its corrosponding result. I will use this, unless someone else has a better option. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sum data that is only within a specific number range? | Excel Worksheet Functions | |||
how to format only a specific character or number in each cell withina range of cells | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
How to add one number to a range of numbers | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |