Basic Percentage issue: HELP!!!

  • Thread starter Thread starter Romileyrunner1
  • Start date Start date
R

Romileyrunner1

Hi, got a problem with the following:
I want to find the percentage of cells that are greater than 24 in a collumn
out of all the cells in the collumn that have an entry (some will be blank) .

Then I want to do the same but only for the cells that satisfy a pervious
requirement from an earlier collumn eg "Female".

SUMPRODUCT((E10:E89="Female")*(GD10:GD89>24))/SUMPRODUCT((E10:E89="Female")*(GD10:GD89>0))

Would this work???
Thanks
RR1
 
Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=AVERAGE(IF((E10:E89="Female")*(GD10:GD89>24),GD10:GD89))

If this post helps click Yes
 
Hi Jacob, thanks for writing.
What you have suggested seems to be finding the average of all the scores
over 24 (females only).
What I need it to do is to give me the percentage of females who have scored
over 24. (not counting any female who has not got any score at all in collumn
GD)
Thanks.
RR1
 
Oops.. Mean while do you have any issues with the below formula

=SUMPRODUCT((E10:E89="Female")*(GD10:GD89>24))/SUMPRODUCT((E10:E89="Female")*(GD10:GD89>0))%

If this post helps click Yes
 
Hi Jacob,
funny thing is I`ve tried something similar to this and got the same result
from it as with your suggestion: it should work , I know but I`m getting
results that are too high, in this case 73% of the females having scored over
24 where it is definitely only 50% ( 7 out of the 14 who have a score entered
in collumn GD). Could you think why I might be getting an error like this?
Thanks.
RR1
 
--Check whether all entries 'Female' are exactly same as 'Female' and not as
'Female ' or ' Female'(with space)

To try out the formula check out with smaller samples with typed in values
and try...

If this post helps click Yes
 
Had another look at parts of the suggested formula.
Crazy as it may sound but what it seems to be doing is on:
SUMPRODUCT((E10:E89="Female")*(GD10:GD89>24))/
it is finding the number of girls that are under 24 including all the non
entries

on:
SUMPRODUCT((E10:E89="Female")*(GD10:GD89>0))
it is giving the total number of females from E10:E89, regardless of if they
have an entry in GD10:GD89 or not????

Any ideas or is my computer on it`s way out????
Thanks
RR1
 
Hi,

It looks like there is a space in the blank cell and therefore GD10:GD89>24
is evaluating to >24 in place of the blank cell i.e. TRUE

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Hi Ashish,
are you saying there are spaces in the blank cells in collumn GD?
I`m running a vlookup formula to these cells in collumn gd to get the
numbers. The vlookup includes an IF(ISERROR(Vlookup.........
)),"",(vlookup......))), so that I get a blank cell in collumn GD rahter than
an error. Could this be giving me problems or spaces. I`ve checked the
vlookups and made sure there is no gap between the ""
..
This is really bizarre.
Thanks
RR1
 
Hi,

I doubt that would be the problem. If you wish, you may mail me the file at
ask(at)ashishmathur(dot)com. Please explain the problem very clearly.
Also, please ensure that it is not a big file

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top