# Trying to assign ABC priority based on size of discrepancy

#### billfinn

##### Board Regular
I'm tracking down some discrepancies and am attempting to prioritize them as ABC or D. I thought this would be simple but...
My criteria is that if a cell value is more than 1000 or less than -1000 it would be classified as an A priority
If >500 and < 1000 or <-500 and >-1000 is a B
If >100 and < 500 or <-100 and >-500 is a C
<100 is a D
I've been scratching my head as to what is causing my issue, but essentially it doesn't assign priority as I expected. the A,B and C priorities assign as expected but D doesn't seem to make it into the decision
Any thoughts or input would be very much appreciated
Thanks,Bill

Code:
``=IF(M2>OR1000<(-1000),"A",IF(M2>AND500<1000,"B",IF(M2<and(-500)>(-1000),"B",IF(M2<and500>100,"C",IF(M2>AND(-500)<(-100),"C",IF(M2<and100>0,"D",IF(M2>AND(-100)<0,"D"))))))))``
</and100></and500></and(-500)>

Last edited:

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Fluff

##### MrExcel MVP, Moderator
=IF(ABS(M2)>1000,"A",IF(ABS(M2)>500,"B",IF(ABS(M2)>100,"C","D")))

#### Fluff

##### MrExcel MVP, Moderator
Alternatively, as I've just learnt
=VLOOKUP(ABS(M2),{0,"D";101,"C";501,"B";1001,"A"},2,1)

#### billfinn

##### Board Regular
Thanks much Fluff! I changed the 0 to a 1 and added 0,"" so blank lines wouldn't zero out but this is just what I needed.
Thanks for your help!
Bill

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback

Replies
3
Views
83
Replies
2
Views
370
Replies
11
Views
816
Replies
4
Views
144
Replies
15
Views
587

1,136,323
Messages
5,675,092
Members
419,549
Latest member
EliteBeat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back