Grade using two range/conditions

ukbulldog001

New Member
Joined
Jul 8, 2015
Messages
23
Office Version
  1. 2021
Platform
  1. Windows
Dear MrExcelits,

Need help in deriving Grade A to E using two sets of data.
Tried my best and stuck.
Please help in formula.
No macros please as I would share this file.

Example as below:

Grade Table to consider:

Book1
BCD
2GRADEQUALITYDELIVERY %
3A1-50098-100
4B501-75095-97
5C751-100092-94
6D1001-130090-91
7E>130085-89
Sheet1


Values to be considered for the Grade:

Book1
FG
3QUALITY636
4DELIVERY96
Sheet1


Result:

Book1
I
3GRADE
4B
Sheet1
Cell Formulas
RangeFormula
I4=IF(OR(G3<500,G4>=98),"A",IF(OR(G3<750,G3>500,G4>=95,G4<98),"B",IF(OR(G3<1000,G3>750,G4>=92,G4<95),"C",IF(OR(G3<1300,G3>1000,G4>=90,G4<92),"D",IF(OR(G3>1300,G4<85),"E","")))))


I have used the above formula but it doesn't work as expected.

Here if Quality=1101 and Delivery=96 then the Grade should be "D".
And if Quality=470 and Delivery=93 then the Grade should be "C".

Thanks in advance.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
So, it seems like whatever the lowest grade is between quality or delivery is what you want it to display, correct? Because in your first example the delivery amount would be grade A. The problem with your formula is you're using IF/OR. one of your two conditions is met for the grade A if you have 1101 quality and delivery of 96.
 
Upvote 0
=if(and(g3>=1,g3<=500,g4>=98),"a",if(and(g3>=1,g3<=500,g4>=95,g4<=97),"b",if(and(g3>=1,g3<=500,g4>=92,g4<=94),"c",if(and(g3>=1,g3<=500,g4>=90,g4<=91),"d",if(and(g3>=501,g3<=750,g4>=98),"b",if(and(g3>=751,g3<=1000,g4>=98),"c",if(and(g3>=1001,g3<=1300,g4>=98),"d",if(and(g3>=501,g3<=750,g4>=95,g4<=97),"b",if(and(g3>=501,g3<=750,g4>=92,g4<=94),"c",if(and(g3>=501,g3<=750,g4>=90,g4<=91),"c",if(and(g3>=751,g3<=1000,g4>=95,g4<=97),"c",if(and(g3>=1001,g3<=1300,g4>=95,g4<=97),"d",if(and(g3>=751,g3<=1000,g4>=92,g4<=94),"c",if(and(g3>=751,g3<=1000,g4>=90,g4<=91),"d",if(and(g3>=1001,g3<=1300,g4>=92,g4<=94),"d",if(and(g3>=1001,g3<=1300,g4>=90,g4<=91),"d","e"))))))))))))))))
 
Upvote 0
If you're happy modifying your table slightly, how about


Excel 2013/2016
BCDEFGHI
2GRADEQUALITYDELIVERY %
3A1100QUALITY470GRADE
4B50197DELIVERY93C
5C75194
6D100191
7E130189
Data
Cell Formulas
RangeFormula
I4=INDEX(B3:B7,MAX(MATCH(G3,C3:C7,1),MATCH(G4,D3:D7,-1)))
 
Upvote 0
I figured the grade table was made just to help us understand with a visual. Also, i really need to look into the index formula.
 
Upvote 0
=if(and(g3>=1,g3<=500,g4>=98),"a",if(and(g3>=1,g3<=500,g4>=95,g4<=97),"b",if(and(g3>=1,g3<=500,g4>=92,g4<=94),"c",if(and(g3>=1,g3<=500,g4>=90,g4<=91),"d",if(and(g3>=501,g3<=750,g4>=98),"b",if(and(g3>=751,g3<=1000,g4>=98),"c",if(and(g3>=1001,g3<=1300,g4>=98),"d",if(and(g3>=501,g3<=750,g4>=95,g4<=97),"b",if(and(g3>=501,g3<=750,g4>=92,g4<=94),"c",if(and(g3>=501,g3<=750,g4>=90,g4<=91),"c",if(and(g3>=751,g3<=1000,g4>=95,g4<=97),"c",if(and(g3>=1001,g3<=1300,g4>=95,g4<=97),"d",if(and(g3>=751,g3<=1000,g4>=92,g4<=94),"c",if(and(g3>=751,g3<=1000,g4>=90,g4<=91),"d",if(and(g3>=1001,g3<=1300,g4>=92,g4<=94),"d",if(and(g3>=1001,g3<=1300,g4>=90,g4<=91),"d","e"))))))))))))))))

Thanks @jondavis1987 works like a charm.
 
Upvote 0
If you're happy modifying your table slightly, how about

Excel 2013/2016
BCDEFGHI
2GRADEQUALITYDELIVERY %
3A1100QUALITY470GRADE
4B50197DELIVERY93C
5C75194
6D100191
7E130189

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Data

Worksheet Formulas
CellFormula
I4=INDEX(B3:B7,MAX(MATCH(G3,C3:C7,1),MATCH(G4,D3:D7,-1)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Thanks @Fluff it works but table is used for visual ref only and I don't want to change the format of the file, since I'm sharing it to other people it would be hard for them to ref.
 
Upvote 0
Assuming the numbers in the table won't change, here is another formula that I think should work...

=CHAR(64+MAX(LOOKUP(G3,{1,501,751,1001,1301},{1,2,3,4,5}),LOOKUP(G4,{85,90,92,95,98},{5,4,3,2,1})))
 
Last edited:
Upvote 0
Assuming the numbers in the table won't change, here is another formula that I think should work...

=CHAR(64+MAX(LOOKUP(G3,{1,501,751,1001,1301},{1,2,3,4,5}),LOOKUP(G4,{85,90,92,95,98},{5,4,3,2,1})))

This too works, thanks a lot @rick-rothstein :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top