• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested If formula with Comparison Operators.

Hi Harish ,

I think your formula is not correctly framed. In the first part of the IF :

=IF(AND($A21<35,$A21<=C$15),$K$15,IF($A21<=C$14,$K$14,IF($A21<=C$13,$K$13,IF($A21<=C$12,$K$12,IF($A21<=C$11,$K$11,IF($A21<=C$10,$K$10,IF($A21<=C$9,$K$9,IF($A21<80,"A","A+"))))))))

the two conditions which are being ANDed are :

$A21 < 35

$A21 <= C$15

C$15 contains 45.

Thus when A21 reaches 35 , the first condition is no longer satisfied. Is this correct ?

Should it not be :

OR($A21<35,$A21<=C$15)

Narayan
 
Hi Harish

To add to what Narayan has said if you add another table (UPside down) your formula could be this

=VLOOKUP(A21,$M$8:$N$15,2,1)

See Orange in file.

Take care

Smallman
 

Attachments

Hi Harish ,

I just had another doubt ; your cutoffs are as follows :

45 , 50 , 54 , 59 , 63 , 68 , 73 , 80

The operator signs you have shown are :

<= , <= , <= , <= , <= , <= , <= , >

If we start from the bottom , this means that for marks :

less than or equal to 45 - F
between 46 and 50 inclusive - D
between 51 and 54 inclusive - C
between 55 and 59 inclusive - C+
between 60 and 63 inclusive - B
between 64 and 68 inclusive - B+
between 69 and 73 inclusive - A
above 80 - A+

What happens to marks between 74 and 80 inclusive ?

A similar problem comes up if we start at the top and work our way down the table.

Narayan
 
NARAYANK991 said:
Hi Harish ,

I think your formula is not correctly framed. In the first part of the IF :

=IF(AND($A21<35,$A21<=C$15),$K$15,IF($A21<=C$14,$K$14,IF($A21<=C$13,$K$13,IF($A21<=C$12,$K$12,IF($A21<=C$11,$K$11,IF($A21<=C$10,$K$10,IF($A21<=C$9,$K$9,IF($A21<80,"A","A+"))))))))

the two conditions which are being ANDed are :

$A21 < 35

$A21 <= C$15

C$15 contains 45.

Thus when A21 reaches 35 , the first condition is no longer satisfied. Is this correct ?

Should it not be :

OR($A21<35,$A21<=C$15)

Narayan
Click to expand...
Thanks Narayan for prompt response.

As per requirement the condition framed correctly, I need both the conditions TRUE,
1. A Student never get D grade less then 35 marks.
2. if I replace the AND operator with OR operator and if Suppose the C$15 contains 25 and C$14 contains 33 after calculation in table 2 then student will get D GRADE only on 33 % of marks. Which will be a wrong grading.

It is working perfectly with AND Operator.

I simply asking, Can we replace the (nested if) with any other formula to get the desired output. (like Arrays or other formulas)

Thanks
 
Hi Harish ,

If you say so.

Regarding my next post , that issue still remains ; if you examine your formula , the last part of the formula is as follows :

IF($A21<=C$9,$K$9,IF($A21<80,"A","A+"))

Here C9 is 73 , K9 is A ; what this means is that between 68 and 73 ,and also between 74 and 79 , the student gets an A ; only if he / she gets 80 and above , the grade is an A+.

This is not going to be possible with a straightforward array formula , since it does not follow a pattern. If you can change the setup of your table 2 , it might be possible ; otherwise , the lower end anyway needs the IF construct ; the upper end needs another IF construct ; it is only the portion in between that can be converted to an array formula. The overall formula will not be significantly simpler.

Narayan
 
NARAYANK991 said:
Hi Harish ,

I just had another doubt ; your cutoffs are as follows :

45 , 50 , 54 , 59 , 63 , 68 , 73 , 80

The operator signs you have shown are :

<= , <= , <= , <= , <= , <= , <= , >

If we start from the bottom , this means that for marks :

less than or equal to 45 - F
between 46 and 50 inclusive - D
between 51 and 54 inclusive - C
between 55 and 59 inclusive - C+
between 60 and 63 inclusive - B
between 64 and 68 inclusive - B+
between 69 and 73 inclusive - A
above 80 - A+

What happens to marks between 74 and 80 inclusive ?

A similar problem comes up if we start at the top and work our way down the table.

Narayan
Click to expand...

In this case we can consider A grade between 69 to 79. for which i have framed a manual condition in if condition

IF($A21<80,"A","A+")

We can simply say :

1. if marks is >=80 then Grade is A+ (Exception: if the Marks is already exceed the 80s limit the A+ will be awarded after whichever value is exist in the Cell C$8, in this case the A grade limit will be the 80%.

2. if the Marks is <35 then Grade will is awarded F only but after natural calculation of the percentage it goes upto whatever value (shown in H$15 for sub_6) and awarded F Grade.
 
Hi Harish

If you use a VLookup you can use the True operator to give you the result you want. The formula is pretty efficient. All you need to do is have a lookup table in another sheet. The True part of Vlookup is seldom used but is really handy if you structure your table for its use.

Take it easy

Smallman
 
Hi Harish ,

After your latest post , I am more confused ; first let us get the limit situations clarified.

You have two absolute limits , a lower limit of 35 , and an upper limit of 80.

Along with this , you also have values in a table , where the lowest row has values :

45 , 53 , 50 , 30 , 49 , 54 , 52

Similarly , the upper most row has the values :

80 , 87 , 85 , 80 , 80 , 80 , 80

Do you mean to say that an F grade is given if the marks are less than the maximum of 35 and the values in the lowest row ?

Similarly , is an A grade given only if the marks exceed the maximum of 80 and the values in the upper-most row ?

Narayan
 
Smallman said:
Hi Harish

To add to what Narayan has said if you add another table (UPside down) your formula could be this

=VLOOKUP(A21,$M$8:$N$15,2,1)

See Orange in file.

Take care

Smallman
Click to expand...

Thanks Smallman,

your suggestion is quite satisfactory, But the solution is not deemed to be fit for my case, because i have 2500 students and each students have minimum 7 subjects. can you imagine how much manual calculation needs to be done for this problem in table 2.
 
NARAYANK991 said:
Hi Harish ,

After your latest post , I am more confused ; first let us get the limit situations clarified.

You have two absolute limits , a lower limit of 35 , and an upper limit of 80.

Along with this , you also have values in a table , where the lowest row has values :

45 , 53 , 50 , 30 , 49 , 54 , 52

Similarly , the upper most row has the values :

80 , 87 , 85 , 80 , 80 , 80 , 80

Do you mean to say that an F grade is given if the marks are less than the maximum of 35 and the values in the lowest row ?

Similarly , is an A grade given only if the marks exceed the maximum of 80 and the values in the upper-most row ?

Narayan
Click to expand...
Yes Sir Exactly!

F grade will be awarded :

for Sub_1 1 to 45
for Sub_2 1 to 53
for Sub_3 1 to 50
for Sub_4 1 to 40
for Sub_5 1 to 49
for Sub_6 1 to 54 and
for Sub_7 0 to 52

Similarly
A+ grade will be awarded :

for Sub_1 80 and above
for Sub_2 87 and above (Natural Calculation is exceed the 80%)
for Sub_3 85 and above
for Sub_4 80 and above
for Sub_5 80 and above
for Sub_6 80 and above
for Sub_7 80 and above
 
Hi Harish,

Just have a look of attached file (Sheet 2). I had made some mior changes in your Grade Table.
1. I had made your grading up-side down.
2. I had inserted an extra row.

For your required formula I had use LookUP() function.

Regards,
 

Attachments

Somendra Misra said:
Hi Harish,

Just have a look of attached file (Sheet 2). I had made some mior changes in your Grade Table.
1. I had made your grading up-side down.
2. I had inserted an extra row.

For your required formula I had use LookUP() function.

Regards,
Click to expand...
Mishra Sir,

You have done fantastic but forgot the operators given in table 2:

your formula result is shown as below :


Grade GP Sub_1 Result of Lookup
F 0 0
F 0 45 49 F Grade should be less than or equal to 45
D 4 50 53 It should be less than or equal to 50
C 5 54 58 It should be less than or equal to 54
C+ 6 59 62 It should be less than or equal to 59
B 7 63 67 It should be less than or equal to 63
B+ 8 68 72 It should be less than or equal to 68
A 9 73 79 It should be less than or equal to 73
A+ 10 80

1. Similarly in other 6 subjects the Grade are not properly distributed.

2. Sub_2 onward A Grade is not awarded to anyone.

Mishra sir, I will be hanged out by the Students if I have printed out the same grade card for the students.

Thanks
 
Hi Harish ,

I am not so sure that this formula is better than what you already have !

=IF($A21<=MAX(35,MIN(C$8:C$15)),$A$15,IF($A21>=MAX(80,MAX(C$8:C$15)),$A$8,INDEX($A$9:$A$14,MIN(IF($A21>C$10:C$15,ROW(C$10:C$15)-MIN(ROW(C$10:C$15))+1)))))

is to be entered as an array formula , using CTRL SHIFT ENTER. Enter this in B21 , and copy down and across for the grades. For the grade points , you will have to modify it accordingly.

Narayan
 
Hi Harish


I was on my way home from work. Your problem should be a formula challenge, then the guys who do formula well come into their own. This bit;

can you imagine how much manual calculation needs to be done for this problem in table 2
Click to expand...

I think I can - this;

=VLOOKUP($A21,C$8:$K$15,COLUMN()+7,1)

The only complication is the 7 needs to come down by two each time. I have outlined this in the file. The set up of your table is the important part.

These results look OK to me but I could be wrong.

Take care

Smallman
 

Attachments

Somendra Misra said:
@Harish Sharma

What grade will be when marks for subject 1 is in between 73 and 80?

Regards,
Click to expand...

Misra Sir,

Let me explain:

1. I mean to say that F grade is given if values in the lowest row of the table 2 (i.e. 45) and if these marks are less than 35 then 35 is the maximum limit of the F Grade.

2. Similarly , is an A grade given only if the marks exceed the maximum of 80 and the values in the upper-most row. (i.e. 87 for Sub 2 and 85 for Sub 3)

Hence the actual grade for the subject 1 will be

1 to 45 - F
46 to 50 - D
51 to 54 -C
55 to 59 - C+
60 to 63 - B
64 to 68 - B+
69 to 79 - A AND
80 and above A+

But in Sub-2

87 is the Cell Value,
Here the A Grade will be awarded 82 to 86 % of marks, Hence the A+ Grade will be awarded only after >87% Marks not after 80%

Hope you got my point.

Harish
 
NARAYANK991 said:
Hi Harish ,

I am not so sure that this formula is better than what you already have !

=IF($A21<=MAX(35,MIN(C$8:C$15)),$A$15,IF($A21>=MAX(80,MAX(C$8:C$15)),$A$8,INDEX($A$9:$A$14,MIN(IF($A21>C$10:C$15,ROW(C$10:C$15)-MIN(ROW(C$10:C$15))+1)))))

is to be entered as an array formula , using CTRL SHIFT ENTER. Enter this in B21 , and copy down and across for the grades. For the grade points , you will have to modify it accordingly.

Narayan
Click to expand...

Narayan Sir,

It is the tremendous use of Min and Max withing the if construct, it was far away from my mind. Thanks for refining my formula.

But a little problem is occurs in Grading where we have applied index function, if you look at the table -2 for Subject 1 there are some operators for assigning grading:-

1. We are assigning the F grade to students' who secure 1 to 45% Marks (formula working Absolutely perfect)

2. D Grade is assigning to those students' who secure Grater then 45 and less then equal to 50 Marks as per table -2 (But problem is, on applying the above given formula the D Grade is assigned only those students who have secured Grater then 45 and less then 50 Marks resulting the D Grade is ending in 49% of marks (The Equal to sign is ignored by the formula) Similarly for other Grades C, C+,B, B+........ and so on.

A+ Grade is perfectly working in all the subjects.

Regards Harish
 
Somendra Misra said:
@Harish Sharma

Have you checked my second file?

Regards,
Click to expand...
Mishra Sir,

Thanks for such a nice formula. It is working perfectly.

But at the Upper range of Grading i.e A+ Grade can not be assigned to anyone if he/she scores Less than 80% marks in our sheet for Subject 1 a person can get A+ Grade after securing 74% marks, (if we calculate the %age through formula, as you have typed the 79 % manually in Cell C9 in Table 2) simultaneously in the sub- 4,5,6 and 7, rest two subject i.e Sub 2 and Sub-3 is correct.

Thanks

Harish
 
NARAYANK991 said:
Hi Harish ,

It is possible I have not understood your problem ; see the uploaded file , which has the same formula.

Narayan
Click to expand...
Narayan Sir,

Thank you Very much. Now it is solved.

I don know i have copy and paste the formula as per your direction, but the equal to sign was extra, particularly in the (INDEX($A$9:$A$14,MIN(IF($A21>=C$10:C$15,) part of the formula.

anyways now it is perfectly working and going to implement.

Thanks once again.

harish
 
You must log in or register to reply here.
Back
Top