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?

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?

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?

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?

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.

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.