| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Basic Percentage issue: HELP!!!
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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
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:GD8924),GD10 :GD89)) If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
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 "Jacob Skaria" wrote: 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:GD8924),GD10 :GD89)) If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
Oops.. Mean while do you have any issues with the below formula
=SUMPRODUCT((E10:E89="Female")*(GD10:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890))% If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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 "Jacob Skaria" wrote: 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:GD8924),GD10 :GD89)) If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
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 "Jacob Skaria" wrote: Oops.. Mean while do you have any issues with the below formula =SUMPRODUCT((E10:E89="Female")*(GD10:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890))% If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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 "Jacob Skaria" wrote: 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:GD8924),GD10 :GD89)) If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
--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 --------------- Jacob Skaria "Romileyrunner1" wrote: 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 "Jacob Skaria" wrote: Oops.. Mean while do you have any issues with the below formula =SUMPRODUCT((E10:E89="Female")*(GD10:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890))% If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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 "Jacob Skaria" wrote: 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:GD8924),GD10 :GD89)) If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
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:GD8924))/ it is finding the number of girls that are under 24 including all the non entries on: SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) 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 "Romileyrunner1" wrote: 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 "Jacob Skaria" wrote: Oops.. Mean while do you have any issues with the below formula =SUMPRODUCT((E10:E89="Female")*(GD10:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890))% If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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 "Jacob Skaria" wrote: 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:GD8924),GD10 :GD89)) If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
Hi,
It looks like there is a space in the blank cell and therefore GD10:GD8924 is evaluating to 24 in place of the blank cell i.e. TRUE -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
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 "Ashish Mathur" wrote: Hi, It looks like there is a space in the blank cell and therefore GD10:GD8924 is evaluating to 24 in place of the blank cell i.e. TRUE -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Basic Percentage issue: HELP!!!
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 "Romileyrunner1" wrote in message ... 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 "Ashish Mathur" wrote: Hi, It looks like there is a space in the blank cell and therefore GD10:GD8924 is evaluating to 24 in place of the blank cell i.e. TRUE -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... 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:GD8924))/SUMPRODUCT((E10:E89="Female")*(GD10:GD890)) Would this work??? Thanks RR1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Percentage Issue | Excel Discussion (Misc queries) | |||
| basic question - how to calculate a cumulative percentage? | Excel Worksheet Functions | |||
| Basic question - how to calculate a cumulative percentage | Excel Discussion (Misc queries) | |||
| Another Visual Basic/Dependant Cells issue | Excel Worksheet Functions | |||
| Calculating a percentage with the end percentage in mind | Excel Discussion (Misc queries) | |||
Linear Mode
