Can somebody help me. I am trying to determine the percentage of times a number appears based on certain criteria. I tried labeling it in the attached spreadsheet to further explain. Thank you in advance.
Gary
Can somebody help me. I am trying to determine the percentage of times a number appears based on certain criteria. I tried labeling it in the attached spreadsheet to further explain. Thank you in advance.
Gary
See attached.
Basic formula is ...
=COUNTIFS($A$2:$A$150,">=" & LEFT(G$1,3)+0,$A$2:$A$150,"<=" &RIGHT(G$1,3),$C$2:$C$150,">=" &(LEFT($E3,5)),$C$2:$C$150,"<=" &(RIGHT($E3,5)+0))/$F3
I have changed some the data formats to enable the formulae used. I round the data in column A to 1 decimal place.
Refer to the attached workbook to see what I have done.
First of all I separated the ranges that you had in column E, so the lower point is in E and the upper point in F, then I put this formula in G2:
=COUNTIF(C:C,">="&E2)
and this one in G3:
=COUNTIFS(C:C,">="&E3,C:C,"<"&E3+1000)
which was copied down to G9, rather than have individual amounts within the formulae.
I also changed the headings in H1:N1, by just having the lower number of the range, 0, 3, 4 and so on. I applied Custom Formatting to these values, so they appear like "0 -->", "3 -->" and so on, to indicate a range, with a different custom format for the final value (8 +). Then I put this formula in H2:
=COUNTIFS($A:$A,">="&H$1,$A:$A,"<"&I$1,$C:$C,">="&$E2)/$G2
copied across to M2. N2 contains this variation:
=COUNTIFS($A:$A,">="&N$1,$C:$C,">="&$E2)/$G2
as there is no upper constraint. H3 contains a slightly different formula:
=COUNTIFS($A:$A,">="&H$1,$A:$A,"<"&I$1,$C:$C,">="&$E3,$C:$C,"<="&$F3)/$G3
and this can be copied across to M3 and down to row 9. Again, the formula in N3 is slightly different:
=COUNTIFS($A:$A,">="&N$1,$C:$C,">="&$E3,$C:$C,"<="&$F3)/$G3
and this can be copied down to N9 also, to complete your table.
Hope this helps.
Pete
Guys, I really appreciate both solutions- more complicated than I could have figured out but now that I see what you did I can follow the logic. However it does lead me to another question. If I wanted to insert another column between 5 & 6 (a data point starting at 5.5) how would I do that? I tried doing it with Pete's and excel wanted a whole number. When following John's the percent's sum (column O) did not equal 100- unless of course I messed something up. The revised columns would look like this:
>3 3-3.9 4-4.9 5-5.4 5.5-5.9 6-6.9 7-7.9 8+
See attached
One formula in my original was wrong.
I have added the new criteria.
Thanks John but when I expanded the data in columns A, B, C- I went from 150 to 866 the resulting percents don't add up to 100.
Updated version which should make it easier to amend
Change the 150 in the ranges to 1000, ... last file I posted
=COUNTIFS($A$2:$A$1000,">=" & H$1,$A$2:$A$1000,"<=" &H$2,$C$2:$C$1000,">=" &$E3,$C$2:$C$1000,"<=" &$F3)/$G3
Use FIND/REPLACE $150 to $1000
Last edited by JohnTopley; 12-07-2015 at 04:53 PM.
I tried but its not working. I'm sure it's me but I can't figure it out. See attached with 886 data points in columns A, B, C up from the original 150.
The "problem" is with the data in column A which has more than one decimal place so for example a value of 3.97 is omitted because it is > 3.9 (in the 3.0 to 3.9)range) but < 4.0 (the next range).
The choice you have is round the values to 1 decimal place OR change the comparator range to (for example) 3.00 to 3.99
I the attached I have rounded the data to demonstrate you get the right results.
YOU DA MAN!!!!!!!!!!!!!!!!!!!!!! Thank you so Much!!!!!!!!!!!!!!!!!!!
Version with changed limits. I note there are 4 entries in A of -0.2 which do not get counted.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks