Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
hi...
Sir, For Eg: i have 1000 students...i entered marks to all the students now i need to fine the total students who have score 50 and <60 in each subject.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
If scores are in Column B, =SUMPRODUCT(--(B1:B100050),--(B1:B1000<60)) -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=496327 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
One way ..
Assume the marks are listed within B2:B100 Enter in C1: 50, in D1: 60 Then put in E1: =SUMPRODUCT(($B$2:$B$100C1)*($B$2:$B$100<D1)) E1 will return the required count of students with marks 50 & < 60 Adapt to suit .. We could also copy E1 down to return other corresponding counts for other ranges of marks by inputting the required mark limits in C2:D2, C3:D3, etc -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Sarath.Ch" wrote in message ... hi... Sir, For Eg: i have 1000 students...i entered marks to all the students now i need to fine the total students who have score 50 and <60 in each subject.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
Hi, You could also try, =COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"59") HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=496327 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
=COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"59")
Perhaps a slight revision: =COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"=60") -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
=COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"59")
Perhaps a slight revision: =COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"=60") Why? Biff "Max" wrote in message ... =COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"59") Perhaps a slight revision: =COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"=60") -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
=COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"59")
Perhaps a slight revision: =COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"=60") Why? A "tighter" upper limit, to conform more closely to the OP's "<60" ? Fractional marks may always be a possibility, e.g.: students with marks of say, 59.5 would have been excluded from the count if "59" was used. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
Fractional marks may always be a possibility,
Yeah, that's true! Biff "Max" wrote in message ... =COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"59") Perhaps a slight revision: =COUNTIF(B2:B100,"50")-COUNTIF(B2:B100,"=60") Why? A "tighter" upper limit, to conform more closely to the OP's "<60" ? Fractional marks may always be a possibility, e.g.: students with marks of say, 59.5 would have been excluded from the count if "59" was used. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
Glad the explanation was accepted <g !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Biff" wrote in message ... Fractional marks may always be a possibility, Yeah, that's true! Biff |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND DIFFERENCE BETWEEN 50 AND <60
You mention you have different signatures, right? Well, it sounds like that. In that case you need to include the Signature... Probably there is a better way to do it, but I am in the end of my day and my brain is working over time, so here is an idea: Assuming your spreadsheet looks like this: Column A: Column B: Column C: Math 50 John Phy 70 Marie Chem 55 Hollie Math 62 Steve Math 80 George Chem 90 Patricia Phy 55 Robert Chem 55 Catherine Phy 85 Ivette Math 60 Jacob Add a 1 to every row in column D to look like this: Column A: Column B: Column C: Column D: Math 50 John 1 Phy 70 Marie 1 Chem 55 Hollie 1 Math 62 Steve 1 Math 80 George 1 Chem 90 Patricia 1 Phy 55 Robert 1 Chem 55 Catherine 1 Phy 85 Ivette 1 Math 60 Jacob 1 and then the next code where you want the results: =SUM(IF(($A$1:$A$10="Phy")*($B$1:$B$10=50)*($B$1: $B$10<60),$D$28:$D$43)) That should give you the result for all the guys that are 50 <60 in Physics. Just copy it somewhere else and change "Phy" for "Math", and there you go with the Maths, then copy it somewhere else and change "Math" for "Chem" and there you have the results for Chemistry. The only thing you have to do is to press CTRL+SHIFT+ENTER instead of only ENTER, either when paste or edit the formula, since it works with ARRAYS. Now, It only depends on how your organization is. If your signatures are in different Worksheets, then you don't need my code. If everything is together, then you can even modify it to get the results by Name or whatever. If it helps, go on, if not, just ignore it! :) -- Hanzo ------------------------------------------------------------------------ Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955 View this thread: http://www.excelforum.com/showthread...hreadid=496327 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|