Hello all!
I'm trying to break down the ranges, but for some reason I can't get the highlighted to work. I only did the highlighted for an example, but I know they're wrong bc I did a histogram... Any help would be greatly appreciated.
Attachment 296416
The formula is working perfectly. Sort the data by size and highlight the data equal to or greater than 51.0 and LESS than or equal to 61.0 This is exactly 134.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
Hi Blackhawks,
You're count seems like incorrect because there are decimals in the range. the top 69 will not count 69.123 and so on.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I think the problem is that you have gaps and overlaps in your ranges, coupled with the fact that your numbers are not integers.
For example, you have "<50" but then the next range is 51-69. So what happens to numbers greater than 50 but less than 51 ... there are 4 of them.
And then you have a range 80-90, and another, 90-100. So where do you plan to count 90? There's only 1 of it, fortunately.
You should have formulae like:andFormula:
Please Login or Register to view this content.andFormula:Please Login or Register to view this content.for exampleFormula:Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Your first row takes everything LESS than 50
Your second row takes everthing BETWEEN 51 and 69
You are missing everything BETWEEN 50 and 51... (eg. 50.41667, 50.5618, 50.63291... etc.)
=COUNTIFS(B$6:B$656,"<=50") 23
=COUNTIFS(B$6:B$656,">50",B$6:B$656,"<70") 157
=COUNTIFS(B$6:B$656,">=70",B$6:B$656,"<80") 144
=COUNTIFS(B$6:B$656,">=80",B$6:B$656,"<90") 165
=COUNTIFS(B$6:B$656,">=90",B$6:B$656,"<=100") 162
total = 651
this includes all values you may have to adjust boundaries to suit
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
< 50 =COUNTIFS(A$6:A$656,"<50") 51 - 69 =COUNTIFS(A$6:A$656,">=50",A$6:A$656,"<70") 70 - 79 =COUNTIFS(A$6:A$656,">=70",A$6:A$656,"<80") 80 - 90 =COUNTIFS(A$6:A$656,">=80",A$6:A$656,"<90") 90 - 100 =COUNTIFS(A$6:A$656,">=90",A$6:A$656,"<=100") Total
trouble with frequency as you seemed to have used the data is put into the next highest bin if it can match exactly
eg data
1
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2
and you use bins
1
2
then the result is bin 1=1 bin 2 is 10
Here is your workbook and it illustrates the problems that are described above. I sorted the data in order, numbered the values as indicated in your value ranges and amended the formulae to accommodate the decimal overlaps (which is the main problem amongst others.
Take a look and what the others have said will be amply illustrated.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks