Help im confused!!!

Daniel Cremin · Mar 29th, 2002, 06:37 PM

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!!!

daleyman · Mar 29th, 2002, 06:44 PM

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

Daniel Cremin · Mar 29th, 2002, 06:53 PM

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!

daleyman · Mar 29th, 2002, 07:01 PM

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?

Dave Patton · Mar 29th, 2002, 09:22 PM

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.

Dave Hawley · Mar 29th, 2002, 11:51 PM

Hi Daniel

Agree with Daniel about using a Pivot Table, they are a great feature. Rather than combining these with array formulas though I would opt for the Database functions. You can see some uses of these here:
http://www.ozgrid.com/download/default.htm
It's the one called "DFunctionsWithValidation.zip"

Daniel Cremin · Mar 31st, 2002, 05:18 PM

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?

Ivan F Moala · Mar 31st, 2002, 07:09 PM

7 is the Limit BUT there are work arounds

Have a look @ http://www.cpearson.com/excel.htm

Chip has a work around for this...

Daniel Cremin · Mar 31st, 2002, 07:28 PM

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!

Mar 31st, 2002, 07:09 PM	#8
Ivan F Moala MrExcel MVP Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 4,209	7 is the Limit BUT there are work arounds Have a look @ http://www.cpearson.com/excel.htm Chip has a work around for this... __________________ Kind Regards, Ivan F Moala From the City of Sails

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 06:37 PM	#1
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 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!!!

Mar 29th, 2002, 06:44 PM	#2
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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

Mar 29th, 2002, 06:53 PM	#3
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 64	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!

Mar 29th, 2002, 07:01 PM	#4
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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?

Mar 29th, 2002, 09:22 PM	#5
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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.

Mar 29th, 2002, 11:51 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Daniel Agree with Daniel about using a Pivot Table, they are a great feature. Rather than combining these with array formulas though I would opt for the Database functions. You can see some uses of these here: http://www.ozgrid.com/download/default.htm It's the one called "DFunctionsWithValidation.zip"

Mar 31st, 2002, 05:18 PM	#7
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 64	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?

Mar 31st, 2002, 07:28 PM	#9
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 64	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!