A few days ago, we posted an online event which would cater to the needs and issues faced by our teachers - especially advisers - in consolidating their MLESF which was hosted online using Google Forms. Basically, the issue arose in the consolidation of the different combinations in items D2, D3 up to the latter part of the survey form.
The event was hosted online, via Google Meet, the session lasted about 2 hours where I tried to go deep details in the consolidation process of the MLESF if I were to be an adviser. The post of that event in our Facebook page an be seen below.
Now, after a few days, I decided to host another event, but this time, it's gonna be via YouTube, the disadvantage of this would be the internet connectivity issues which also resulted for our streaming to be cut-off into 2 parts. Videos can be seen below.
- Cell Destination (Cell Column/Row)
- Sheet Name (The desired name of the sheet you used)
- Original Reference Response (what text/spelling was used in the original data)
- Position (where you will be pasting the formula to be used)
Explanation:
=COUNTIF - refers to the logical argument
'Form Responses 1'X:X, - refers to the source column where the gender is found
A2 - where the cell of the text "Male" is found
C2. Highest Educational Attainment (HEA)
We needed to customize a table in this section of the demonstration such that the different choices of the Highest Educational Attainment options from the MLESF must be completely identical with the table data we will be creating.
The first table was named HEA_Table which basically was the different HEA options placed in each rows of cells A7 to A13. Beside it would be the equivalency of each item which is now under column B, consequently, it's on B7 to B13.
The second table was named HEA_Results which would be the reference table for each equivalency once the tallies will be determined. This will be used as a guide to our vlookup formula in determining between the three (i.e. Father, Mother, Guardian) has the highest educational attainment.
For the formulas used, we gave 1 column dedicated for each item, Father Column which started on A16, Mother Column on B16, and Guardian Column on C16.
Formula on A16 (1st response for Father Column): =IFERROR(VLOOKUP('Form Responses 1'!AO2,HEA_Table,2,0),"")
Explanation:
=IFERROR( - refers to the 1st logical argument such that if the result is erroneous
VLOOKUP( - refers to the 2nd logical argument to search for one's value
Form Responses 1'!AO2, - where the first father HEA is found in the raw data
HEA_Table, - the 1st customized table we created
2, - the column number in the table that should show in the result
0), - the condition set which is to look for the exact match referred to by the raw data which also ends the vlookup logical argument.
"") - the result that should reflect if the vlookup argument should present an erroneous product.
I hope that makes sense, but just look at the demonstration if you get a bit confused.
The same formula is used for the Mother Column, but instead of having the reference to be Form Responses 1'AO2, it should refer to the first cell in the raw data of the mother column's HEA. In the example, it's found in the AR column, as such, the formula would be: =IFERROR(VLOOKUP('Form Responses 1'!AR2,HEA_Table,2,0),"")
For the Guardian Column, the formula would be: =IFERROR(VLOOKUP('Form Responses 1'!AU2,HEA_Table,2,0),"")
In order to determine the HEA for each item, we added another column beside the Guardian Column and named it as HEA Result. Here, we will determine among the 3 different results of the formula above would have the highest value. To convert that into a formula, we used: =MAX(A16:C16)
Explanation:
=MAX( - the logical argument used in the formula which will look for the maximum value being compared in the range of cells.
A16:C16) - refers the to set of cells (range) which will be compared and the parenthesis ends the argument.
The result of this formula must then be determined in order to count each HEA in the set of responses. As such, another vlookup was used, but this time, it must refer to the second table we created. In E16, we used this formula: =IFERROR(VLOOKUP(D16,HEA_Result,2,0),"")
Explanation:
The formula is basically the same as the one used previously, thus, the explanation of each variable is technically the same. The changes only occurred in D16 which is where the HEA Result is found, and the table name which now refers to the 2nd table created previously.
On cell D7, we counted each of the items categories in the HEA so that this will be used as the reference in the Summary Matrix of our MLESF. The formula being in D7 is: =COUNTIF($E$16:$E$561,C7)
Explanation:
Since this is another COUNTIF formula, the explanation and use is technically the same as well. We are looking for the range of E16 to E561 which are the determinant results. We locked this range using the dollar ($) symbol so that even when we drag the formula down to D13, the range it will be referring to will not change.
D2. Instructional Support
We started to populate the raw responses in this section under cell K7 of our sheet. We merely used a reference formula just to copy the raw responses. The formula used is as simple as: ='Form Responses 1'!BL2
After that, we drag this formula down to K552 which is the last available raw item that can have a response. What we did was to Highlight cell K7 up to K552, and pressed CTRL D in our keyboards to immediately populate the formula.
In cell L6 to cell R6, we copied the responses found in the MLESF Online Form and distributed it in each cell. We needed to be sure that each cell must have a complete and identical data that is found in the raw responses options, this is to ensure that the formula will be able to determine whether the response of each raw data is identified under each category.
In cell L7 we used this formula: =IF(ISNUMBER(SEARCH(L$6,$K7)),1,0)
Explanation:
=IF( - the logical argument which will have a result of either 1 or 0 depending on the attained conditions or arguments that followed.
ISNUMBER( - the 2nd logical argument, it is used to determine if the result of the proceeding formula will be a number or not. The results will either be TRUE or FALSE.
SEARCH( - the 3rd logical argument, it is used to identify if a certain text in a particular cell is found in another cell. Basically, it will be comparing 2 different cells.
L$6, - the text that is to be searched for, it has a $ symbol in its row number to indicate that the row is constant.
$K7) - the cell/text where the first item will be searched on, the $ symbol in the column indicate that it is constant.
) - the parenthesis that will close the "ISNUMBER" logical argument.
,1, - the result if the argument is TRUE from our "IF" logical argument.
0) - the result if the argument is FALES from our "IF" logical argument.
This formula will then be populated until R7. So, we select cell L7 where this formula is used and then highlight it to cell R7. When all cells are highlighted, press CTRL R on your keyboard to populate the formula.
Now that all formula is populated, we highlight all cells from R7 down to R552 which is the last item of our responses, and press CTRL D to populate the formula for every category.
With all these, the results become apparent whether the raw responses are available in each column category by giving 1 and 0 as their results.
We skipped column S of our sheet to make a divider and colored it just to be clean with our setup.
Then, we copy pasted all of the combinations found in our Summary Matrix in Cell T6 to Cell AI6.
In the first combination, we have Parent/Guardian Only which is under column T, the first result should be placed in cell T7. Remember that the raw response is already copied in cell K7, this means, we won't need to go back to the Form Responses Sheet just to check the references.
With that, we derived this formula to determine if the Parent/Guardian Only column should have a result that is 1 or 0 by using:
=IF(AND(R7<>0,SUM(L7:R7)>2),0,IF(OR(ISNUMBER(SEARCH("sibling",K7)),ISNUMBER(SEARCH("grandparent",K7)),ISNUMBER(SEARCH("member",K7)),ISNUMBER(SEARCH("others",K7))),0,IF(ISNUMBER(SEARCH("guardian",K7)),1,0)))
Explanation:
Okay, this one is going to be a bit troublesome if you're not familiar with MS Excel formulas. But what we're trying to do is to maximize the use of the IF formula and the ISNUMBER(SEARCH formula all at the same time. We created multiple criteria using conditional arguments of AND and OR within our logic, this is so that the accuracy of determining the responses will fit the combination that is identified in this particular column.
First Criteria
AND(R7<>0,SUM(L7:R7)>2)
This criteria basically says that, cell R7 must not be equal to 0 and the sum of cells L7 to R7 should be greater than 2.
The reason for that is, the set of combinations in the Summary Matrix does not involve the item of "able to do independent learning"; which means, any response that has this item combined with another item is and should not be under the Parent/Guardian Only column. The next condition would be that the result must be greater than 2, which means that the sum of total responses will never have more than 2 combinations even if we are looking for only 1 category. This is because we already negated (equal to zero) the item under able to do independent learning.
The ISNUMBER(SEARCH Criteria:
The argument is looking for specific texts as was explained previously. We specified these texts to be either "sibling", "grandparent", "member" etc, and should be referenced to K7 which is the raw response. The point being, since we are only looking for 1 particular response in this category, if any of these texts should be found in our raw response, it must automatically give us a result of 0. And of course, if all of these arguments are proven to be achieved, then the result should always be 1.
The same idea is used in the succeeding categories which is on Parent/Guardian and Siblings which used the formula:
=IF(AND(R7<>0,SUM(L7:R7)>3),0,IF(ISNUMBER(SEARCH("member",K7)),0,IF(AND(ISNUMBER(SEARCH("guardian",K7)),ISNUMBER(SEARCH("sibling",K7))),1,0)))
For the Parent/Guardian, Siblings & Grand Parent which used the formula:
=IF(AND(R7<>0,SUM(L7:R7)>4),0,IF(ISNUMBER(SEARCH("member",K7)),0,IF(AND(ISNUMBER(SEARCH("guardian",K7)),ISNUMBER(SEARCH("sibling",K7)),ISNUMBER(SEARCH("grandparent",K7))),1,IF(AND(ISNUMBER(SEARCH("guardian",K7)),ISNUMBER(SEARCH("grandparent",K7))),1,0))))
We only added more categories to look for or removed categories depending on the demand of the combination.
With this, we can determine if each of the category will have 1 or 0 as its responses. These responses will all then be summed up to get its total result and these will be referenced in the Summary Matrix.
So, that's basically it, we hope we were able to provide you with assistance and a guide to complete the formula you can use in order to tally the different responses in your Advisers MLESF.
If you have any more questions or clarifications, you can just post it down below.
0 Comments