How can I figure out which cells are less than a given value in excel ?

[avijit74]

I have a range of cells A1 to A10 with different values. Now I want to figure out those cells which contain numbers less than 25. I do not want to highlight them but rather want to know which particular cells matches the criteria. Say it will point out that A1, A6, A7 contain values less than 25.

[Pepe Le Mokko]

And how do you want them pointed out other than highlighting or changing formatting?

[AliGW]

Try this:

=IF(COUNTIF($G$2:$G$8,"<"&25)>=3,"Fail",IF(COUNTIF($G$2:$G$8,"<"&25)=COUNT($G$2:$G$8),"Pass","Retest"))

Your duplicate thread has been closed. Please do NOT open duplicates in future.

[avijit74]

I'm very sorry for the duplicate thread. Actually it was posted accidentally during some network problems. Please let me know the way to delete that thread. I shall do it immediately. Once again I apologise.

[avijit74]

Thanks a lot Ali ! Your formula is correct. But I want to know if the result is "Retest" then for which subjects. It should mark out subjects. Like "Retest English, Mathematics" or "Retest Mathematics, Science".

[JeteMc]

Please attach the file from post #1 in the closed thread to this one.
Let us know if you have any questions.

[avijit74]

I have subjects in column 'E', full marks i column 'F' and marks obtained by a student in column G. Column 'I' has a formula suggested by AliGW which determines when a student Passes, Fails or goes for retest. If a student gets less than 25 marks in not more than two subjects, he is sent for retest. AliGw's formula does the work fine. But I want the subjects to be mentioned for which a student is sent for retest. Say, 'Retest Eng., Maths' or 'Retest Eng., Science' etc. Is this possible ?

[JeteMc]

Perhaps the following will help:
1. Select cell I2
2. Paste the following array entered formula* into the formula bar:

Please Login or Register  to view this content.

3. *Press and hold the Ctrl and Shift keys while pressing the Enter key.
4. Drag the fill handle down to I8.
Note that an option would be to put the number 25 in a cell, say J1, and reference that cell as in:

Please Login or Register  to view this content.

Let us know if you have any questions.

[avijit74]

This is not working. You didn't get me. Actually I was saying that if a student gets less than 25 in any subject (not more than two), he is sent for retest. The formula should point out for which subject he is sent for retest. Suppose a student gets 20 marks in Mathematics but gets above 25 in all other subjects. Obviously he is sent for retest in Mathematics. So the formula should mention the subject also for which he is sent for retest.

[AliGW]

Are you expecting this all in one cell or in a column adjacent to his results?

[avijit74]

Yes, that's it. In cell I1.

[JeteMc]

Here is the formula applied to the file that you sent. Math and Geo. both have marks of 12 so I would assume that those are the ones that you want to see in the list in column I.
Let us know if you have any questions.

[avijit74]

Thanks a lot JeteMc ! Your solution is working fine but is it possible without the reference cell 'J1' and the total thing in one cell 'I1' separated by commas. Say " Retest English, Mathematics" or "Retest Bengali, Geo."

[JeteMc]

Yes, use the first formula given in post #8 for cells I2:I3 (you could format the text to match the fill as modeled) then modify Ali's formula to read:

Please Login or Register  to view this content.

Let us know if you have any questions.

[avijit74]

Thank you so much JeteMc ! You solved my problem. This would help me a lot in my School administrative work. Hope to get more help from you in near future ! Good luck and stay healthy !

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[avijit74]

Hi JeteMc ! I am writing this because I was unable to put your solution in my original score sheet. Please put the formula you suggested in this attachment. Conditions are same. Consider the 'total marks' for each subject. Put the formula in AF7 with your modifications. Conditions are same as post # 14.

[JeteMc]

Those formulas will not work for this setup, however here are two that will:
For cell AF7:

Please Login or Register  to view this content.

For cells AG7:AH7*:

Please Login or Register  to view this content.

*Array entered formula.
Let us know if you have any questions.

[avijit74]

Hi JeteMC ! You see when subject 'Beng.' score is below 25, it's returning 'O' in AG7. Please tell me the formula if condition is " Beng.<50 and other subjects should be <25 ie, M7<50 & (P7,S7,V7,Y7,AB7,AE7)<25.

[JeteMc]

Paste the following array entered formula* into cell AG7: =IF(M7<50,J4,IFERROR(INDEX($N$4:$AC$4,SMALL(IF(($P7:$AE7<25)*($P$5:$AE$5="TOT"),COLUMN($N$1:$AC$1)-COLUMN($M$1)),1)),""))
Paste the following array entered formula* into cell AH7: =IFERROR(INDEX($N$4:$AC$4,SMALL(IF(($P7:$AE7<25)*($P$5:$AE$5="TOT"),COLUMN($N$1:$AC$1)-COLUMN($M$1)),IF(AG7="Beng.",1,2))),"")
*Remember to active each formula by holding the Ctrl and Shift keys while pressing the Enter key.
Let us know if you have any questions.