% error calculating grades

M

Marie1uk

I am using the formula below to calculate the % of males (denoted as "M"
in the F column) scoring A*, A, B,.... U (contained in the H column) in
a list:

=SUMPRODUCT(--($F$48:$F$432="M"),--($H$48:$H$432="A*"))/
COUNTIF($F$48:$F$432,"?*")

In my test I find that if I have 4 entries - 3 male & 1 female it will
report that 25% of males scored A*, 25% scored A and 25% scored B,
instead of 33.3% for 3 boys only. It is including the female score when
doing the % calculation. How can I isolate the boys only scores ?
 
D

duane

how about

=SUMPRODUCT((F48:F432="m")*(H48:H432="a*"))/SUMPRODUCT((F48:F432="M")*1
 
M

Marie1uk

That solution does not isolate the A grades from A*grades (A and A* ar
read identically) and also gives blank scrores a reading of 100%
Thanks anyways
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

General Excel question 24
count across 3 columns 8
Formula than understands midnight? 26
pull out certain data 1
Adding data only if adjacent to cell labeled "male"/"female" 1
Find matching records in two tables using mulitiple criteria 1
Solution to tricky problem of showing exactly Top N rows in Pivottable 0
COUNT and SUM (please help!) 11

Top