Excel find if Value is within range and assign number 1

[ftpdoo]

Hi,

I have a column with numeric values ie:

Column
723
6
2
1342
234234

and I want to say if it falls within the range > 1000 then assign the value 10, or if 500-999 assign the value 8 and so on.

Is this possible in Excel?


The full list of values are:

>1000 = 10
500-900 = 8
250-499 = 6
100-249 = 4
0-99 = 2

Thank you in advance for any help or guidence.

Jonny

 

[DPlank]

Assuming your figures are in Column A, you could use an IF formula with AND involved:

=IF(A1>=1000,"10",IF(AND((A1<=999),(A1>=500)),"8", IF(AND((A1<=499),(A1>=250)),"6",IF(AND((A1<=249), (A1>=100)),"4",IF(A1<=99,"2","Invalid entry")))))

However, this doesn't really let you analyse the numbers any more easily, does it. What are you trying to achieve with this?

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[ftpdoo]

WOW -Thank you so much!!! This was exactly what I was trying to do! EXCELLENT!

 

[SkipVought]

I'd use a table and the MATCH function with a match type of 1 or -1, depending how the table is sorted.

It is a better practice to put data range data in a table rather than hard-coded.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[DPlank]

Quite agree with Skip, I was actually trying to say that although it's possible to simply build a basic formula to do this, there are many better ways to go about it, depending on what you want to achieve.

Hence the final note of 'what are you trying to do?'

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393