Getting error circular reference help

Hi,
I want to find out the top 5 students in the class. I have a array formula but it's giving error circular reference. So the formula is not giving require output.

Any help will be appreciated.

Thanking you.

aj8 =

Please Login or Register  to view this content.

Hi oeldere,
Thank you. The serial number column 'ai' has nothing to do with this table. It's only the serial numbers.

If I apply the formula correction you supplied then it gives the output 'John' in the column 'aj8' which should be the roll number of the student in column 'ak8'

So there is a link between roll number and name of the student and not any relation with serial numbers.

Thank you.

Please add manualy the expected result in your file (and refer to the depending cells, with an color).

I have inserted a required output in the file. The reference columns are:

Roll no.: column C
Name of the student: Column D
Total: Column Y
Percentage: Column AA
Grade: column Z

Hope this will help you to get the output of top 5 students in the class by array formula.

Thank you.

Hi, Pl check out the attachment

I used the following formula (see the attached file)

Rank
Index / Match
Vlookup

Hi Boopathiraha,
Thank you. It's absolutely as per my requirement but it's not updating after changing the total marks in column 'Y'. It should automatically update after changeing the total marks in column 'Y'.

Please do the necessary changes to get desire output automatically.

Thanking you.

Hi oeldere,
Thank you. It's working fine but a little revision is required. Sorry for forgeting the relationship of the column 'fail in subject' remark. It's important because though the student has more marks than the student than a student who is passed in all subject remark 'P all' the 'P all' student should have a first number.

I have inserted a column 'Fail in subject' for your reference and to get correct output. I have also inserted a column 'output' showing the names of students who are passed in all subjects and should have a top 5 students in the class.

Sorry for forgetting to mention this important condition. The numbering should be on the column 'fail in subjects' as follows:

P all
F1
F2
F3
F4
F5
F6
F7
F8
F9

In this sequence the ranking should be arrange.

Sorry for trouble.

Thank you very much.

See the attached file.

Please reply.

Hi oeldere,
Excellent! It's working fine but a little amendment is require. If there are less than 5 'P all' students are in the table then it should take the next student 'F1' 'F2' and so on as per sequence till 5 students in class.

Thank you for revised file.

Thanking you.

After this last amendment the problem will be solved.

See the attached file.

Please reply.

Hi oeldere,

Good morning!

I appreciate you for your sincere efforts to solve this problem.

There is a data in column AG Remark. Please don’t use it. If you need any helping column then insert it in between the two tables.
Column AH is a blank column separating these two tables from each other. If you need any helping column then insert it in between these two tables.
Column AI is for serial numbers. I want the serial numbers there from 1 to 5 for first 5 topper students. So don’t use it. If you need any helping column then insert it in between these two tables.

In this way the helping columns will be Ai, AJ and AK; and the second table will start from column AL.
Column AO Fail in subjects should show the remark as I already mentioned in my last post as below:

Sequence:

P all, F1, F2, F3, F4, F5, F6, F7, F8, F9. F stands for ‘Fail’. So the numbering sequence should be as I mentioned here.

First P all in descending order of total marks;
Secondly F1 in descending order of total marks;
Thirdly F2 in descending order of total marks;
Fourthly F3 in descending order of total marks;
Fifthly F4 in descending order of total marks;
Sixthly F5 in descending order of total marks;
Seventhly F6 in descending order of total marks;
Eighthly F7 in descending order of total marks;
Ninthly F8 in descending order of total marks;
Tenthly F9 in descending order of total marks.

So the final output should be as follows:

SR. NO. ROLL NO. NAME OF STUDENT TOTAL PERCENTAGE GRADE FAIL IN
SUBJECTS
1 3 Tom 618 82.4 A P All
2 4 Sally 598 79.73 A P All
3 5 Dolly 574 76.53 A P All
4 2 Sam 574 76.53 B P All
5 1 John 154 20.53 A F1


If the final output is as per the output sample then the problem will be solved today.

Hope this will clear you my requirement. If you have any doubt then immediately ask me. I will provide you necessary information at the earliest to complete the task.

Thanking you.

See the attached file.

Please reply.

Hi oeldere,
Excellent! Now it's working absolutely fine as per my requirement. There are about 75-80 students in the class. If I delete the formula from sixth student then the output is not correct. So we have to retain the formula from the sixth student which data is not necessary. Only the first 5 students who scored top are require. So is it possible to remove these extra unnecessary students from sixth student? If so, otherwise leave it. I will hide these cells.

Please let me know so that I will mark the thread solve now.

Thanking you.

You could try it yourself.

Remove the row with the data of the 6th student.

What's happening?

Please reply (for information to other forummembers).

Hi oeldere,
Thank you for solving the problem.

I appreciate you for working hard on my problem since yesterday and achieve the target.

I deleted the row from sixth student then I didn't get correct output. So the entire data is necessary to get correct output.

As per my knowledge the best option to get only 5 toppers is to hide the unnecessary data manually.

Thank you.

Show the file with the deleted rows.

Please also add the desired (expected) result.

Hi oeldere,
Really I appreciate your Patience and sincere efforts to get the perfect output.

I have deleted the extra rows with formulas. Now it's not giveng correct output as shown in the table right to main table. See the students serial number 4 and 5 which are incorrect. It should as per require output that is F1 students.

File attached.

Thank you.

Do not remove cells AG - AI and try again.

Please reply.

Excellent! Now it's working fine.

The problem is solved but just for my knowledge do explain the formula.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Does the same action.

So what is the difference between these two formulas?

Thank you.

Hi oeldere,
Good morning!

Extremely sorry for trouble to amend the formula. After detailed study of the table I observed following mistake.

See sr. no. 5:

Output require:

5 6 Robert 611 81.47 A F3

Present result:

5 3 Tom 600 80 A F3

In this example there is an injustice on Robert who scored 611 marks > Tom 600 and both are fail in 3 subjects F3. So the fifth number should be of Robert because he scored greater than Tom.

Is it possible to avoide injustice on Robert?

In this same manner all the remarks should be arrange in a descending order of total marks if the remark is same like it.

For example:

P all in descending order of total marks.
F1 in descending order of total marks.
F2 in descending order of total marks.
F3 in descending order of total marks.

And so on …

Sorry and thank you.

The values in colum AS are manuly inputted, the values are in cells AU17:AX20.

See the attached file.

Sorry, I didn't understand because there are so many formulas.

Please send me a clean final output file for my better understanding. Don't disturb my columns because there is a another information in my original file.

Thfank you.

in #23 is a clean final output.

You only have to fill the column AS with the figures (values) in cells AU17:AX20.

Then see if the result is you suppose it should be.

Sorry! Still confused.

The table is disturb hence I'm not understanding it easily.

Please amend the file I attached the last post without disturbing the columns for my better understanding.

Sorry for trouble.

Thank you.

Why don't you amend your file, so you can use my solution?

On which formula you want an explaination.

Please post the english formula on the forum.

The formula for total. See post 21 for reference. I have posted the two formulas achieving the same target.

In this file I didn't understand the amended formula.

Just for me creaate a table using it and achieve the target.

Thanking you,

ao8=

Please Login or Register  to view this content.

=154 + 8/1000000

Result is 154,000008

ao8

Please Login or Register  to view this content.

Result is 154


We use this formula to make

Y9 and Y12 have both the same result 574

With the above formula we create a diverance so we can rank the value

Dear oeldere,

Good morning!

Thank you for formula explanation.

Extremely sorry and I apologize for not understanding your solution. I'm feeling very sorry for the same since yersterday. The thread is going too long as it's marked solved before wich is not good for us. I wish to close it today only positively with your kind cooperation as till now.

I'm optimistic that it will be the final post for the same problem. Please see my post 22 in which I requested you to amend the file to avoide injustice on Robert for the fifth number in top 5 students in the class.

Please do insert the new amended solution in the file attached post 22 for my better understanding and avoide confusion.

Thank you for your continuous cooperation and guidance to achieve the final output.

Do send me a final amended file without disturbing the columns and format of the table.

Thanking you in anticipation.

See #27

Please Login or Register  to view this content.

You did not reply on that question.

Dear oeldere,
Thank you for the concern about my problem. I appreciate you for your kind cooperation and guidance to solve the problem.

Now there is not a problem of formulas. I'm unable to understand the disturb sheet because of my personal problem as mention you by pm.

The thread is going too long after marking solved which I feel sorry so I didn't continue my problem again.

So hereby I humbly request you again to create a final output file without color formatting for me.

I'm optimistic that you will understand my problem and send me a final file with a solution.

Thank you very much for your concern about my problem.

Have a nice day!

Please Login or Register  to view this content.

If there is no problem with the (understanding of the) formula you can change the formula to your need.

If you have a problem with understanding the formula (and I think you have), try to understand the formula and you could solve this kind of problems also next time.

We try to learn members to upgrade their Excel-skills, not only to make a solution to their excel file problems.

posted twice