1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded education professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

"A" question for those who Excel

Discussion in 'Computing and ICT' started by Trendy Art, Mar 11, 2017.

  1. Trendy Art

    Trendy Art Star commenter

    I have scratched my head as to how I can use a COUNTIF to differentiate between A* and A grades on a tracker.

    It's easy to count the number of A* grades with a COUNTIF. But, if you use Find and Replace using A* as the criteria, it searches all the A grades as well!!

    Which leads to this problem:

    e.g. if there's 2 A grades and one A* grade in the range of C2:C46
    =COUNTIF(C2:C46,"A*") returns 1 - OK
    =COUNTIF(C2:C46,"A") returns 3 - counts both the 2 A and one A* grade, even though A* is NOT A!!!

    Is there anyone out there touched by genius that can help me on this one?
     
  2. rizvani04

    rizvani04 New commenter

    =COUNTIF(C2:C46,"A")-COUNTIF(C2:C46,"A*") maybe?
     
  3. T0nyGT

    T0nyGT Lead commenter

    I would recommend storing the exact grade in an integer and using a lookup table to say whether it's an A or A*. Then use your COUNTIF to look at the mark range rather than the grade string
     
    Trendy Art and wanet like this.
  4. wanet

    wanet Star commenter

    Or subtract A* from the "A"s to gave the correct answer.
     
    Trendy Art likes this.
  5. Trendy Art

    Trendy Art Star commenter

    Thank you both - what are the chances of having two genius answers...? :)
     
  6. GeordieKC

    GeordieKC Occasional commenter

    Try =COUNTIF(C2:C46,"A~*")

    The three characters that cannot be used in a search string are ~, ? and *, but you can use them if you precede them with a ~
     
  7. tjra

    tjra Occasional commenter

    Change the A* to an A+ .
     

Share This Page