Help require to insert countif function and shorten the formula

Hi friends,
In the formula the if conditions for cell ‘g8, m8, s8, y8, ae8, ak8, aq8, aw8, bc8 are >=3 then I want to enter 15 in cell h8 if cell g8 contains 20. I want to use countif function to count >=3 to achieve the target. I also want to shorten the formula because it’s too long and I have to add some more conditions if the cell g8 contains 21 to 34. The formula is in cell h8.

The formula is as follows:

=IF(G8=20,IF(M8>=35,IF(S8>=35,IF(Y8>=35,IF(AE8>=35,IF(AK8>=35,IF(AQ8>=18,IF(AW8>=18,IF(BC8>=18,15)))))))))

Any help will be appreciated.

Thank you.

You could shorten it slightly by using MOD instead to define your ranges.
However, I think what you really need is just learn how to organize your formulas so that they're easy to read and troubleshoot.

You can arbitrarily use Alt+Enter within the formula bar to move to the next line, and then enter a number of spaces to keep things aligned.

Here's my version, which utilizes an array formula:

=IF(G8=20,
           IF(IF(MOD(ROW(M8:AK8),7)=0,M8:AK8)>=20,
           IF(IF(MOD(ROW(AQ8:BC8),7)=0,AQ8:BC8)>=18,15)))

Array formulas are confirmed with Ctrl+Shift+Enter to exit the cell.

And here's your version, also organized for clarity.

=IF(G8=20,
          IF(M8>=35,
             IF(S8>=35,
                IF(Y8>=35,
                  IF(AE8>=35,
                     IF(AK8>=35,
             IF(AQ8>=18,
                 IF(AW8>=18,
                    IF(BC8>=18,15)))))))))

This has no impact on your formula, but just makes managing it much easier.

Hi daffodil11,
First of all I thank you for providing me tips to make my formula more easier to read and understand. I will definately follow your guidelines in future.

Thank you for shortening the formula. However you missed a main condition that the should be passed in minnimum 3 subjects to get grace marks for passing.

I want to use this formula for all subjects.
The total of grace marks is limited to 15 only.
The table of grace marks is as follows:
Average ... Grace
20: 15
21: 14
22: 13
23: 12
24: 11
25: 10
26: 9
27: 8
28: 7
29: 6
30: 5
31: 4
32: 3
33: 2
34: 1
35: 0

It's same for all subjects and the total of all should be 15 only.

I'm attaching my workbook here for your better understanding.

Thanking you in anticipation.

This is slightly shorter, but you did not give the last "else" argument?
=IF(G8=20,IF(AND(M8>=35,S8>=35,Y8>=35,AE8>=35,AK8>=35),IF(AND(AQ8>=18,AW8>=18,BC8>=18),15)))

Perhaps this...
=IF(G8=20,IF(AND(M8>=35,S8>=35,Y8>=35,AE8>=35,AK8>=35),IF(AND(AQ8>=18,AW8>=18,BC8>=18),15,""),""),"")

Dear Fort,
Thank you for revised version. In this formula you missed 1 condition 'The student should be passed in minimum 3 subjects having marks >=35 and >=18 as mentioned in the formula.' In short the student should passed in 3 subjects then only he will get grace marks if needed. The one more important restriction is that the total of grace marks should be <=15 only. So instead of 'and' I think 'or' formula is necessary with a condition of passing in minimum 3 subjects is cumplsory. I want to use this formula for all subjects and the total of grace marks should 15 only.
thanking you.

Originally Posted by mso3

Dear Fort,
Thank you for revised version. In this formula you missed 1 condition 'The student should be passed in minimum 3 subjects having marks >=35 and >=18 as mentioned in the formula.'

Both daffodil and myself based our suggestions of what you provided - we left nothing out and we added nothing.

This part...
=IF(G8=20,IF(AND(M8>=35,S8>=35,Y8>=35,AE8>=35,AK8>=35),IF(AND(AQ8>=18,AW8>=18,BC8>=18),15)))
replaces your nested IF's...
=IF(G8=20,IF(M8>=35,IF(S8>=35,IF(Y8>=35,IF(AE8>=35,IF(AK8>=35,IF(AQ8>=18,IF(AW8>=18,IF(BC8>=18,15)))))))))
so both need 5 cells to contain at least 35

and this part...
=IF(G8=20,IF(AND(M8>=35,S8>=35,Y8>=35,AE8>=35,AK8>=35),IF(AND(AQ8>=18,AW8>=18,BC8>=18),15)))
replaces t
=IF(G8=20,IF(M8>=35,IF(S8>=35,IF(Y8>=35,IF(AE8>=35,IF(AK8>=35,IF(AQ8>=18,IF(AW8>=18,IF(BC8>=18,15)))))))))
here, both require 3 cells to contain at least 18

If this is not what you want, then perhaps your initial formula was in error?

Hi Fort,
Thank you. To count the 3 subjects in which the student is passed I requested you to insert countif function. Doesn't matter. There are 9 subjects.

The passing condition is:
For first 6 subjects the student is passed when he obtain >=35 marks and
for last 3 subject the student should obtain >=18 marks.
The first 6 subjects are of 100 marks and the last 3 subjects are of 50 marks. So the passing is >=35 and >=18.
For grace marks:
The student must be passed in any 3 subjects.
Then he will get maximum 15 marks in a subject or two or more subject limit to 15 total only. Don't divid the subjects in 2 groups only the jpassing condition is different because of the marks.

I think now my requirement will be clear to you to modify the formula to meet the condition.

Thank you for taking trouble to solve the problem.

You may try this.....

=IF(SUM(G8>=35,M8>=35,S8>=35,Y8>=35,AE8>=35,AK8>=35,AQ8>=18,AW8>=18,BC8>=18)>=3,15,0)

Does this help?

Dear sktneer,
Excellent! It is working fine but a little revision is necessary to meet all the criterias mentioned in the thread. Please see the grace mark table. The total of grace marks must be limit maximum to 15. In short each student will get only maximum 15 grace marks. It may be in a single subject or divided into other subjects to meet the criteria of passing that is 35 and 18.

I enter this formula in all subject’s grace column but it’s adding 15 marks to all 9 subjects. The total of grace marks is 135 which is too high. It should be maximum 15 for each student.

Please modify it.

Thanking you in anticipation.

You can rearrange you sheet by having only one Grace column at the end where Total summary begins. So if a student gets the grace mark based on the rules, 15 will be entered in the grace column. Because you don't have any set rule for giving grace numbers to a particular subject.
As far as I think, grace marks are given when adding those marks will make a student pass in a subject. Because grace marks in fact are not the reward to a student. (correct me if I am wrong).
So when a student is passed in all the subjects, does he/she need grace marks? Therefore if you ask me, student needs the grace marks only when necessary to get passed in the subject.
What do you say?

Dear sktneer,
Here’s a clarification of your questions:

If the student is passed in all subjects then there is no need of grace marks. So it should be zero.
For each subject a grace column is necessary because we have to show the student/parents in which subject; how many marks are given as grace to promote him/her to get pass. And the total of all these marks must be limit to maximum 15 only. It’s not necessary to give 15 marks in a one subject. The 15 marks may be divided in any other subjects to get pass in that subject limit to 15 total. For example:

S1 g1 .. s2 g2 .. s3 g3 .. s4 g4 .. s5 g5 .. s6 g6 .. s7 g7 .. s8 g8 .. s9 g9 .. total marks total grace marks
33 2 .. 30 5 .. 33 2 .. 56 0 .. 65 0 .. 45 0 .. 14 4 .. 17 1 .. 42 0 .. 335 14

It’s not necessary that all 15 grace marks should be given to the student. It may be <15. Doesn’t matter. The marks should be automatically given to the student according to the marks obtain by the student in each subject as the grace mark table given in my last post. So there is a need to set a rule for all subject as per the requirement of the marks in that particular subject limit to 15 only. It’s not necessary that the student must be pass in all subjects with a grace mark. He may fail in one or more subjects if the 15 grace marks are less for him to get pass in all subject. For this I have already created a formula to show the progress of student as f1, f2, f3, f4, f5, f6, f7, f8, f9, p all. ‘f’ stands for fail; ‘p’ stands for pass; ‘s’ stands for subject; ‘g’ stands for grace marks.

Hope this will clear your doubts to modify the formula to achieve the target automatically.

Thanking you.

The formula used in the sheet will cause circular reference. So you need to enable Iterative Calculation. To do so Click on the Office Logo (top left of the spread sheet) --> Excel Options --> Formulas --> Under the Calculation Options --> Check the box before Enable Iterative Calculations.

Grace columns between columns H and AL will have the following formula.....
In H8

=IF(SUM($G8>=35,$M8>=35,$S8>=35,$Y8>=35,$AE8>=35,$AK8>=35,$AQ8>=18,$AW8>=18,$BC8>=18)>=3,IF(G8<35,IF($BL8>=35-G8,35-G8,0),0),0)

and then copy down.

For Grace columns between columns AR and BD
In AR8

=IF(SUM($G8>=35,$M8>=35,$S8>=35,$Y8>=35,$AE8>=35,$AK8>=35,$AQ8>=18,$AW8>=18,$BC8>=18)>=3,IF(AQ8<35,IF($BL8>=18-AQ8,18-AQ8,0),0),0)

and then copy down.

Since this formula is comparing marks of Average column which is average of two tests of a subject i.e. (Test1+Test2)/2, you will need to double the grace marks in order to make him pass.
Take the example of G13 in Average column. It has 34 so it seems it is only 1 less than the passing marks 35. But since this is average of Test A = 23 (D13) and Test B = 45 (E13) which tells that this student is passed in Test B but failed in Test A as he has got only 23. So when you average these two test's marks you get 34 [(23+45)/2] but to get average of 35 he must have the total of 70 in these two tests so in fact you need to add 2 in the total of Test A and Test B to get the average of 35 i.e. [((23+2)+45)/2]. That is my assessment. If you think it is correct, you will need to make a change in the formulas suggested above like.....

In H8

=IF(SUM($G8>=35,$M8>=35,$S8>=35,$Y8>=35,$AE8>=35,$AK8>=35,$AQ8>=18,$AW8>=18,$BC8>=18)>=3,IF(G8<35,IF($BL8>=(35-G8)*2,(35-G8)*2,0),0),0)

Same way you will need to change the formula for last three subject.

Notice the column BL, there I am using a formula to hold the max grace marks left for a student.
In BL8

=15-SUM(H8,N8,T8,Z8,AF8,AL8,AR8,AX8,BD8)

and copy down.

As per your conditions only two students have got grace marks. Test the formulas with more set of data to see if you are getting the desired results.

Dear sktneer,
Good morning!

Excellent! The first formula is working fine as per my requirement because we have to show how many grace marks he has received for that particular subject. We have nothing to do with test 1 and test 2; instead we have to think of the average marks of that particular subject that in this case 34+1 and not 2.

What is the relation of the formula in column bl with the grace marks of each subject? Because if I delete the formula in column bl then the grace marks in each subject change to zero which is wrong. If it's necessary I will change the last unused column in my original resultsheet.

Cells ar8, ax8, bc8 are showing -15 grace marks total -45 which should be 0, 0, 0 total 0 because the student is passed in all subjects ‘p all’ so there is no need of grace marks.
Cell bc9 = 32 passed no need of grace marks in cell bd9; instead of 0 it’s showing -14 which is wrong. What is the problem?

Can we remove the relationship of column bl in the formula because there is already a another data in my original resultsheet. It’s not blank for our use.Because of this -14 the total of grace marks is giving wrong output.
Cell bg10= 0 which should be 9 and no need for last 3 subjects which are showing -3 for each which is wrong.

Please remove these problems then the problem will be solved today.

Thanking you.

Column BL is necessary for the formula. You can used any other column as per your choice with the same formula in col. BL.
Refer my attached sheet, AR8,AX8 and BC8 are showing 0 grace marks. As I mentioned the formula is different for the last three subject.

=IF(SUM($G8>=35,$M8>=35,$S8>=35,$Y8>=35,$AE8>=35,$AK8>=35,$AQ8>=18,$AW8>=18,$BC8>=18)>=3,IF(AQ8<35,IF($BL8>=18-AQ8,18-AQ8,0),0),0)

Moreover to make the formula work correctly, make sure that you have enabled iterative calculations.

Dear sktneer,
I have enabled iterative calculations.
Still the error continues. Please see the attached workbook and suggest me the correction. Is it a array formula?

Thanking you.

You have wrong formula in BL column.
It should be.....

In BL8

=15-SUM(H8,N8,T8,Z8,AF8,AL8,AR8,AX8,BD8)

and copy down.

You probably do not read the whole reply or don't pay full attention to the instructions before applying the formulas in the sheet.

Hi sktneer,
Sorry for trouble. Now the problem is about to solve but I don't know what is wrong. I'm following your guidelines carefully but the same error is occuring repeatedly. I'm also feeling very sorry for that.

Please check the file again and do the revision and send me a revised version to avoide any mistake from me.

Dear sktneer,
Please see the revised version, check it and solve the problem for last 3 subjects.

Thank you and sorry for trouble.

That is so strange that the formula is evaluating correctly but the values are not shown correctly in the cells. Like AR9 should be 4 but showing 0. Something wrong with the sheet, not sure what it is.
Make a fresh sheet with the same formula, that will work. (I tried it)
You said that the last three subject are of 50 marks but you are showing marks in those subject more than 50. How it is possible?
I think you need to make a change in the formula also for AR8 onward (for last three subjects) like this.....

=IF(SUM($G8>=35,$M8>=35,$S8>=35,$Y8>=35,$AE8>=35,$AK8>=35,$AQ8>=18,$AW8>=18,$BC8>=18)>=3,IF(AQ8<18,IF($BL8>=18-AQ8,18-AQ8,0),0),0)

Dear sktneer,
Good morning!

Sorry! The last 3 subjects ar of 50 marks. By mistake I have inserted the marks more than 50. I tried it with a fresh sheet but still unable to achieve the require target. Since last 2/3 days we are working on it but unable to achieve the target. We are very close to it but don't know why we are not getting it. What's wrong?

If it's not possible then is there any other easy alternative approach to achieve it? I think one is to create a separate condition for each that is about 15/16 for each for first 6 subjects and 3 for last 3 subject. It will be too lengthy.

Do you have any other idea for it? Please provide me your fresh revised sheet to see whether it's working fine on my pc or not.

Please think on it and solve my problem at the earliest. We are wasting a lot of time on it.
Thanking you in anticipation.

Please find the attached sheet. This time I have used a VBA code (on module1) to calculate the grace marks. You will find a button on Grace Test sheet, click this button to populate grace marks for each student. See if you get the desired output as per your requirement.
Earlier the Iterative Calculation along with sheet's Automatic Calculation was causing the error, so I decided to go for an alternative method, VBA code and I think which is more reliable also. See if this works correctly for you.

Hello sktneer,
Thank you very much for solving the problem.

I appreciate you for yor kind help.

Thank you.

Dear sktneer,

Excellent! Excellent! Excellent!

Now it’s working absolutely fine as per our expectation. I don’t want to run the macro each time instead I want to do it automatically after entering the marks in the column. In the final output of the result sheet the average and grace mark columns will be hidden. So I don’t want any color formatting in these columns. I want the cell formatting in the column total as follows:

The plus sign and grace marks should be in red color. The text should be red and no pattern formatting is required.
The plus sign and grace marks should be superscript. I have added the code for it. Now it’s not working automatically because we run the grace macro manually. If it will run from sheet module then it will superscript the plus sign and grace mark automatically.
For example:
If the total column contains 32+3 then the output should be 32+3

Two more conditions are require as follows because the rules changes time to time as per education policy structure of grace marks.

A student will get maximum 10 grace marks in a single subject. In short if the student has 23 marks then he will not get 12 grace marks but he will get only 10 grace marks. He will not pass in that particular subject. In this case after giving grace marks if the student is not reaching the passing marks that is 35 and 18 then no need to give grace marks; though the total of grace marks is not 15.
Secondly, the student will get grace marks maximum in only 4 subjects. These 4 subjects should be the highest. If the student has 6 subjects in which he is eligible of grace marks then the top 4 subjects get grace marks limit to 15.
These two rules change time to time. So please tell me where to edit the minimum marks and maximum 4 subject’s conditions.

After this final revision the the problem will be solved. I appreciate you for your sincere hard work and knowledge of programming.

Thanking you.

Dear sktneer,
Now I solved the superscript color red problem. See file replaced.

Hi sktneer and excel experts,
sktneer solved the problem of grace marks but I want to add 2 final conditions to it to make it perfect as per my requirement.

Two more conditions are require as follows because the rules changes time to time as per education policy structure of grace marks.

A student will get maximum 10 grace marks in a single subject. In short if the student has 23 marks then he will not get 12 grace marks but he will get only 10 grace marks. He will not pass in that particular subject. In this case after giving grace marks if the student is not reaching the passing marks that is 35 and 18 then no need to give grace marks; though the total of grace marks is not 15.
Secondly, the student will get grace marks maximum in only 4 subjects. These 4 subjects should be the highest. If the student has 6 subjects in which he is eligible of grace marks then the top 4 subjects get grace marks limit to 15.
These two rules change time to time. So please tell me where to edit the minimum marks and maximum 4 subject’s conditions.

After this final revision the problem will be solved.

I request you experts to solve my problem at the earliest to start my work.

Waiting to receive a positive solution from you soon.

Thanking you in anticipation.

Please find the attached sheet to see if this works as per your new conditions.

Hi sktneer,
Thank you very much. I check it roughly. It's working fine. I will check the output tomorrow with different criterias and inform you the final opinion.

Simply tell me where to edit to reduce the subjects 4 to 3 to get grace marks 3 out of 6.

See you tomorrow.

Good night!

Thank you.

You need to take care of this line of code...

If Gcnt <= 3 Then

This will allow grace marks in 4 subjects max.

Dear sktneer,
Thank you.
If I want to give grace marks in max 3 subjects then ...

Code ... Grace marks in max subjects
If Gcnt <= 3 Then ... 4
If Gcnt <= 2 Then ... ?
If Gcnt <= 1 Then ... ?

Please guide to avoide future chage grace marks policy.

Thank you.

If Gcnt <= 3 Then (4)
If Gcnt <= 2 Then (3)
If Gcnt <= 1 Then (2)

Gcnt isn't initialized so the initial value of Gcnt will be 0. For Gcnt<=3 The code will run for Gcnt={0,1,2,3} i.e. max 4 subjects.
Or you can initialize Gcnt=1 before For Each Ocell in Orng line and then for 4 subject you can use If Gcnt<=4.

Dear sktneer,

Good morning!

Greetings from me,

Thank you for guidance to edit the code to meet my requirement. I successfully edited the code to give grace marks in max 3 subjects.

Now the programme is working absolutely fine with correct output without any error.

You have done a excellent job to accomplish a laborious, time consuming job inn a single click. I appreciate you for the same.

Thank you.

You're welcome. Glad that your issue has been resolved. Thanks for the feedback as well.