Further breaking down from countif result

[LarryLee87]

Hi all

I need help with excel.

I am able to get a result from the worksheet to find out how many students have less than 50% attendance by using countif.

Now i had to use the result obtain from countif to pull out the names of students that have less than 50% attendance.

May i know what formula should i use and how do i go about doing it.

Example : Countif reult show 3 students have less than 50% attendance. Who are these 3 student.

Your help is strongly appreciated.

Thanks

[JeteMc]

Welcome to Excel Forum.
Without seeing your set up it is hard to know how to help. You can upload a sample of your spreadsheet by clicking on GO ADVANCED and then scrolling down to Manage Attachments to open the upload window.
That said the following array entered formula* will work with the setup presented in the attached file (so it may be of some help):

Please Login or Register  to view this content.

To activate an array entered formula press CTRL+SHIFT+ENTER, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[LarryLee87]

Hi JeteMc

Thanks for your advice and earlier email.

You may refer to attach to see my set up.

Sheet 1 is the student attendance.
Sheet 2 is the report to indicate how many students have less than 50% attendance.

To further analysis, I will need to know who are the students in sheet 2 results.

Example : who are the 4 students that have less than 50% attendance.

Thanks alot in advance for your help.

[JeteMc]

As applied to you set up the array entered formula could be written as:

Please Login or Register  to view this content.

Let us know if you have any questions.

[LarryLee87]

Hi JeteMC

Thanks for you input.

This is the results i am looking into.

However, I wish to know :

1st : Base on the worksheet, we need to manually deter how many cell is required.

2nd : If there is more than 1 worksheet how do i go about doing it. (You may refer to attach)

Appreciate your help.

[JohnTopley]

See attached:

To keep it simple ....

in "SUMMARY"

Formula in B4

=IFERROR(INDEX(Sheet1!B$30:B$35,SMALL(IF(Sheet1!$R$30:$R$35<50%,ROW(Sheet1!$R$30:$R$35)-ROW(Sheet1!$R$30)+1,""),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter

in C4

=IFERROR(INDEX(Sheet1!C$30:C$35,SMALL(IF(Sheet1!$R$30:$R$35<50%,ROW(Sheet1!$R$30:$R$35)-ROW(Sheet1!$R$30)+1,""),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter


Copy to "Sheet2" in table and change reference in formula to appropriate Sheet.

[LarryLee87]

Hi John

May i know what is ROWS)$1:1.

Thanks

[LarryLee87]

Hi John

May i know what is ROWS)$1:1.

Thanks

[JohnTopley]

ROWS($1:1) acts as a counter; as you drag the formula down this changes to ROWS($1:2) in the second row of the table giving a value of 2, ROWS($1:3) in the next row (3) etc.

This is used by the SMALL function to get the 1st smallest value , then 2nd smallest value etc.

To see how this works, click on a cell containing the formula, then click "Formulas" on the main toolbar, then "Evaluate formulas".

In the "Evaluate Formula" pop-up, click the "Evaluate" button and you will see how this works.

[LarryLee87]

Hi John / Jetemc

Thanks for your help.

I tried doing it on my own but it does not as I had more sheets to analysis.

There is a different from the previous file that i had uploaded.

This time there are more sheets and the column had been shifted from the back to the front.

I will wish to know how can use the result obtain from countif to pull out the names of students that have less than 50% attendance.

Your help is strongly appreciated

[JeteMc]

My proposed solution would be as shown on Sheet1 J7 and K7:K20. In J7 I would use data validation referencing the activity names, in this case the sports and games. The array entered formula that populates K7:K20 is:

Please Login or Register  to view this content.

You'll notice that the formula works well for girls and boy badminton, and then quits. Tracking the reason for that I find that there are a number of cells in column L of the other sports and games roll sheets that have Division by zero errors because the base is zero. I would propose that someone decide what value those should be translated into, such as "N/A", and amend the formula for column L to something like =IFERROR(F30/K30,"N/A").
Note: Conditional formatting, font set to background color, hides the zero results in K7:K20 on Sheet1.
Let us know if you have any questions.

[LarryLee87]

Hi JeteMc

Thanks for your proposal.

May I know how am i able to get the breakdown from other groups?

Do i use the same =iferror or changes need to be made (after i had translate those base that are zero to NA)

[JohnTopley]

Following from JeteMc's reply, see attached.

I created named ranges for the major groups which list the sub-groups: see "Formulas"==>Name Manager.

I then created the drop=downs with formulae.

You need now to add the IFERROR(.....) specified by JeteMc to column L in all the sheets.

NOTE: I removed the apostrophe from "Boys' Brigade" and "Girls' Brigade" as this causes problems with sheet names used in the INDIRECT statement.

[LarryLee87]

Hi John

I wish to check with you. Is it a must to put {}.
If so, why must we put these symbols.
Sorry about it as this is my first time encountering this.

[JohnTopley]

The symbols { ....} indicate an "Array formula": you do not enter them BUT put the formula in a cell and press Ctrl+Shift+Enter together.

If you do this correctly, then the { ...} brackets will appear round the formula.

If you make any changes to the formula you must re-enter using Ctrl+Shift+Enter (CSE)

[LarryLee87]

Hi John

Thanks for your help.

[JohnTopley]

You are very welcome.