• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

counting the number of students within a score range

Murugesh Siva

New Member
Hi,

I am Murugesh, I am a test administrator in Corporate house. I have been trying to pull a pivot and have been get poor results
In a day I run close to a 100 tests with 2 vendors for which I have to compartmentalize the scores in groups of 50's. I used the If(OR(AND formula like

=IF(OR(AND(B:B>=400,B:B<450)),1,"")

while the result is right, it does provide a '1', my pivot says otherwise, I get extras. I am wondering if my formula is right. I also need to post the results in Excel 2003, 2007 and 2013 cause a lot of people are still using 2003.

Thank you
 

Attachments

  • test scores.xlsx
    8.9 KB · Views: 6
What about:
=COUNTIFS(B2:B9,">"&400,B2:B9,"<="&450)
Adjust <, > and = and values as appropriate

Or as a table see attached
 

Attachments

  • test scores.xlsx
    9.8 KB · Views: 5
Hi Murugesh ,

I am not sure what you want your formula to do ; the construct :

OR(AND(B:B>=400,B:B<450))

does nothing more than what the construct :

AND(B:B>=400,B:B<450)

does , since if you want to use an OR , unless you have at least two cases , the OR is not necessary.

Secondly , if you want your formula to look at each value in column B , and put the result in column C , looking at an entire column as your formula is doing is again , not necessary.

A simple IF statement such as :

=IF(AND(B2>=400,B2<=450),1,0)

will do.

Only if you want a complete sum of all the cases where the cell in column B has a value between 400 and 450 , in one cell , do you need to use a formula such as :

=SUMPRODUCT(($B$2:$B$9>=400)*($B$2:$B$9<=450))

Hui , I think COUNTIFS will not work with Excel 2003 , as required by Murugesh.

Narayan
 
A bit of a structural change compared to what @Hui did.

@Murugesh Siva - do you need to this to be able to function as a formula in all of those versions or do you simply need to be able to paste the results? If you didnt' want to have to do it yourself, you can easily write a little macro to copy and paste the values to a new sheet and then send the sheet so you don't lose the template.
 

Attachments

  • test scores.xlsx
    32.6 KB · Views: 1
Back
Top