NEWB- Trying to average data based on specific criteria

[fpgary]

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

[JohnTopley]

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.

[Pete_UK]

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

[fpgary]

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+

[JohnTopley]

See attached

One formula in my original was wrong.

I have added the new criteria.

[fpgary]

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.

[JohnTopley]

Updated version which should make it easier to amend

[JohnTopley]

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

[fpgary]

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.

[JohnTopley]

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.

[fpgary]

YOU DA MAN!!!!!!!!!!!!!!!!!!!!!! Thank you so Much!!!!!!!!!!!!!!!!!!!

[JohnTopley]

Version with changed limits. I note there are 4 entries in A of -0.2 which do not get counted.