Help im confused!!!

Daniel Cremin

Board Regular
Joined
Feb 23, 2002
Messages
64
Hi please could you help me sort through a problem im having with devising a solution to a complex issue!

Lets start at the beginning! It has taken me nearly an hour to write this whole thing in a way that would make sense to you the reader, so if you do decide to read the whole thing, i have tried my best to break it into small easy to understand pieces!!!

~I have an underlying worksheet of student records stored in 30 rows(row 3 being the 1st).

~ I am going to have a Struggling Students report worksheet which lists students who get a D, E, N and/ or U grade on 3 or more of the tests -

~the "Struggling"count of each student's grades, stored in each Student is handled by a Countif Statement, so no probs there.

Heres where it gets a little complex:

In order to be able to have total tally numbers of "Struggling" students from each of the 3 different class groups appear on the brilliant students report, i am going to have a special column of concatenated cell entries on the underlying worksheet which are a combination of the name of the class teacher and the number of "Struggling" performances (e.g. MrDickens4 - because the student's teacher is Mr Dickens and he struggled 4 times)

~ Originally i was going to just have a series of unique IF Statements will be used to evaluate each of the Struggling performances count cells (the one that counts the number of A*s and As)of the 30 student rows on the Underlying worksheet and decide if the number of the row that each IF statement evaluates should be entered or not based on the contents of the cell being greater than or equal to 3.

The first IF statement would have searched the Struggling count cell of the first student row on the underlying worksheet and if the student had struggled 3 or more times would enter the number "1" because this is the number of row that the cell it evaluates is out of the 30 student rows, and if the Student hadnt over-performed 3 times then a "0" would have been entered;

-The second IF statement cell searching the Struggling count cell of the 2nd student record row on the underlying worksheet would have entered "2" if the Struggling performance count had been greater than 3, and if not then a "0" would have been entered.

This trend continues for the other rows all the way down to 30.

OFFSET functions, set to start offsetting from the "cell header" of each column to retrieve information from would have Offset down by the number of rows specifed in the IF statement cell and retrieved this info - if 0 is entered then the "cell header's" text would be entered instead.

A For... Next statement would have been used to evaluate the 30 student IF statements and hide all rows which met the "searchvalue" of "0", so that only the Struggling Students appear.

However there is a very good reasons this no longer can be the case listed below:

I realised that if Less than 30 students are entered, that the 'template' row (that each student row is actually copied from and inserted above - its like a master key!) would also be visible on screen to the end-user due to the Functionality of its Struggling Performance Count cell detecting that 5 U grades are entered in this row (due to no values being entered in the Mark Cells that the Grades are based on via LOOKUP functions).

Here is what i have now come up with:

30 unique rows containg NESTED IF statement cells on the "Struggling Report" worksheet, which are each set to evaluate the CONCATANATED Chemistry Teacher and Struggling Performance Count dell (the combination of these two information items) for a range of possibilities and record the row of the Student being evaluated out of the 30 student rows, so that OFFSET functions using the Value entered in the NESTED IF powered cell as the number of rows to offset down by.

In the the First data row of the "Struggling Students Report" worksheet the Nested IF statemnt would enter the number "1" (because its evaluating the first student on the Underlying Worksheet)if the cell its being set to search (the CONCATANATED Teacher and Struggling Count informatio item)is equal to any of the following:

Mr Dickens3
Mr Dickens4
Mr Dickens5
Mr Temple3
Mr Temple4
Mr Temple5
Mrs Roberts3
Mrs Roberts4
Mrs Roberts5

else = 0

the second set of Nested IF statemnts, evaluating the second student row on the Underlying worksheet would search for the possible combinations already listed above and if they are True enter a "2", but if not any of them then a "0".

This trend continues for the other rows all the way down to 30.

A For...Next Macro could then be used to Evaluate the 30 Nested IF statement cells searching the range for the searchvalue=0 and using an IF... Then statement to hide all rows with the "0" entered in them so that only Struggling Students are displayed.

Basically what i want is any feedback regarding possible probs with implementing something like this and any helpful suggestions!

THANKS SO MUCH FOR READING ALL THIS!!!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Seems to me that is a complex set of functions, which is fine, but i would use either that approach, or vba routine to do the work for you, rather than trying too use both.

Would probably be easier just to do vba routine, mail me the worksheet i'll sort it for you to get you started....

Daley_123@hotmail.com
 
Upvote 0
thanks for the offer, but the thing is that its a project for an Examination Board where they want the main focus of my project to be on Advanced functions - Any VBA programming over the level of Application.quit is probably over the heads of these ppl and they seem to only encourage bits of it. My main concerns are:

~ With the Nested IF statements will Excel allow me to cover all the situations i put down ( You know it being Mr Dickens3, Mr Dickens4, Mr Dickens5, Mr Temple3, Mr Temple4, Mr Temple5, Mrs Roberts3, Mrs Roberts4, Mrs Roberts 5.

- Will the For... Next statement idea work dyou reckon by just hiding rows with IF statemnt column cell entries of "0"?

- Oh and is there any way to make the value If true argument of an IF statement increment by 1 when copied down as this would save me loads of time - i doubt it but still maybe you know some trick i dont!
 
Upvote 0
just to clarify, base situation is that you have 30 student rows, with for example:

column a: student name
column b: teacher name

columns c-z: test result letters

is this what you have?
 
Upvote 0
With one or more helper formulas, a pivot table could probably create a good
report.

=SUM(COUNTIF(C2:H2,{"D","E","N","U"}))
in a column would highlight those students.
 
Upvote 0
cheers ppl - by the way ive ended up deciding to use multiple Nested IF statements to generate 'index numbers' - originally i was just going to have the index number being generated by a basic IF function that looks at a cell and if the value was 3 or greater i would write the position of the row out of 30, to be used in an OFFSET statement. Hoever as the system could have less than 30 students, and the row that all the student rows are based on also contains the "brilliant count" functionality i realised that if less than 30 students were entered that this row would show up on the report!!! So i decided to use CONCATANATE statements that join together the Teacher name and the brilliant count value, as the 'template row' wont have any teacher name entered in it this means that i can just have a series of Nested IF statement that depending on the number of brilliant performance should be able to write the correct number of the student out of 30.

the possible combinations that would match are:

-Mr Dickens3
-Mr Dickens4
-Mr Dickens5
-Mr Temple3
-Mr Temple4
-Mr Temple5
-MrsRoberts3
-MrsRoberts4
-MrsRoberts5

how many Nested if statements can you have though? i need 9 dont i?
 
Upvote 0
thanks yeah i just remembered chip's website a minute ago! But i dont reckon ill need to do it now as i can just have the Year Group Grade totals for each grade being a total of the 3 different classes Countif cells for that grade for each test - absolutetly simple solution but one i completly missed because of the complexity of the project im doing (theres absoluteltly loads of stuff to remember to do!).

I think at the back of my mind it was Chip's website that had told me that there was a limit on the number of IF statements, i guessed there would be ways to beat it but still i was worrying for a minute!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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