Trying to assign ABC priority based on size of discrepancy

billfinn

Board Regular
Joined
Jun 7, 2018
Messages
114
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
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
How about
=IF(ABS(M2)>1000,"A",IF(ABS(M2)>500,"B",IF(ABS(M2)>100,"C","D")))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Alternatively, as I've just learnt
=VLOOKUP(ABS(M2),{0,"D";101,"C";501,"B";1001,"A"},2,1)
 

billfinn

Board Regular
Joined
Jun 7, 2018
Messages
114
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
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top