Nesting more than 7 IF statement

  • Thread starter Thread starter Loay Nakhal
  • Start date Start date
L

Loay Nakhal

If you have about 10 cases to check, then you need about
10 IF function as a nested functions. In Excel you can not
create a formula with more than 7 nested IF statement. So
please send your suggestion to handle more than 7.

With thanks,
Loay
 
Use a different approach.

Depending upon the tests, you can use CHOOSE, MATCH/INDEX, VLOOKUP etc. The
possibilities are many, but we would need to know what you are trying to do.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Students grading:
Give --- For grades
A 375-400
A- 350-374
B+ 325-349
B 300-324
B- 275-299
C+ 250-274
C 225-249
C- 200-224
D 100-199
F 0-99

Please advice,
Loay Nakhal
 
Hi
set up a table like the following for looku (lets say on a separate
sheet called 'lookup'):
A B
1 0 F
2 100 D
3 200 C-
......
10 375 A

Note: This list is sorted ascening ans used only the lower boundary.

Now on your second sheet use the following formula (assumption A1
contains the value to check):
=INDEX('lookup'!$B$1:$B$10,MATCH(A1,'lookup'!$A$1:$A$10,1))
 
Thank you your approach. It works!!!
Loay
-----Original Message-----
Hi
set up a table like the following for looku (lets say on a separate
sheet called 'lookup'):
A B
1 0 F
2 100 D
3 200 C-
......
10 375 A

Note: This list is sorted ascening ans used only the lower boundary.

Now on your second sheet use the following formula (assumption A1
contains the value to check):
=INDEX('lookup'!$B$1:$B$10,MATCH(A1,'lookup'! $A$1:$A$10,1))



--
Regards
Frank Kabel
Frankfurt, Germany




.
Click to expand...
 
Type the following table,

0 F
100 D
200 C-
250 C+
275 B-
300 B
325 B+
350 A-
375 A


say, in A2:B10, on a worksheet named Admin and name the GradeTable.

This would allow you to use:

=LOOKUP(NumericalGrade,Table)

If you prefer, you can have a build-in table in the formula itself and
dispense with Admin:

=LOOKUP(NumericalGrade,{0,"F";100,"D";200,"C-";250,"C+";275,"B-";300,"B";325
,"B+";350,"A-";375,"A"})
 
I would use VLOOKUP.

Setup a table like this#
0 F
100 D
200 C-
225 C+
250 C+
275 B-
300 B+
325 B+
350 A-
375 A


in G1:H10 say.

And if the mark is in A1, in B1

=VLOOKUP(A1,G1:H10,2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Loay

Alternate method. Use Worksheet_change event code.

Ranges will have to be adapted to your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
N = Target.Row
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the range
Select Case rng.Value
Case Is < 20: text = "F"
Case 20 To 35: text = "E"
Case 36 To 50: text = "D"
Case 50 To 65: text = "C"
Case 66 To 85: text = "B"
Case Is > 85: text = "A"
End Select
'Apply the Letter Grade
Excel.Range("B" & N).Value = text
Next rng
End Sub

Gord Dibben Excel MVP
 
Back
Top