hexagon logo
Register Sign in
  • Replies 12 replies
  • Subscribers 30 subscribers
  • Views 290 views
  • Users 0 members are here
Options
Related

Excel counting

craiger_ny
Lets say I have a column of numbers. I want to know how many are less than a certain value. In other words I need to know the count of cells that pass a certain argument or test for being less than (or greater than for that matter) a certain value. How do I do this?
  • You could in another column insert an if argument that will return a value of 1 for greater than your set value or 0 for less than your set value. Then, total the column at the bottom. The sum of all the 1's and 0's would be the total number of numbers greater than your limit.
  • I have a lot of data points, that is a lot of coding. Here is what I have a column of data. On the same sheet I have the nominal and tolerance. I can set a condition like you said to compare the data in a cell to the nominal. The problem happens when I copy (drag) that formula. It will copy it so that the next cell down gets evaluated which I want but unfortunately the nature of the copy compares it to the next cell down from my nominal. I want it to compare all the cells to my nominal. I can do what you are saying but I'd have to code (or edit) each argument for every cell containing data.
  • Try taking a look at the =frequency formula. It's an array formula that I use for making histograms. I don't have a chance right now to look into the details right now but I think that this should be able to do what you are after.

    You can set a $ condtion for the cells in the formula so when you copy it won't change the cell value. ie if nominal value is in cell C47 you can use valuecell>$c$47 it will lock the nominal cell in any copying you do.
  • What about copying your nominal to the cells below it?

    Either that or if your nominal is constant, you could put the nominal in as a number in your formula rather than as a cell reference.
  • Try taking a look at the =frequency formula. It's an array formula that I use for making histograms. I don't have a chance right now to look into the details right now but I think that this should be able to do what you are after.

    You can set a $ condtion for the cells in the formula so when you copy it won't change the cell value. ie if nominal value is in cell C47 you can use valuecell>$c$47 it will lock the nominal cell in any copying you do.

    I'll check it out thanks I'll check it out.

    What about copying your nominal to the cells below it?

    Either that or if your nominal is constant, you could put the nominal in as a number in your formula rather than as a cell reference.

    I want to avoid a spreadsheet with shiit all over the place, I'd rather not clutter things. I was able to use replace to fix the formulas instead of editing each one but my goal is to evaluate a name (set of data).
  • Here is a quicky frequency example that I threw together. I made up a column of numbers and counted the number of values between min nom and max. The frequency formula, function actually, is an array so you have to enter the formula in a special way. select a group of cells that is one greater than the number of subdivisions you have. In this case I have three subdivisions(min,nom,max) so I select 4 cells. With the 4 cells selected type the formula. The first range of cells is the data group, the second range of cells are the subgroup limits. After you enter the formula, CTRL+SHIFT+ENTER to correctly create the array. It will return counts for between 0 and first sub group, first and second subgroup, second and third subgroup, and greater than third subgroup. I know that this is a really cobbled explination and example, if you need more help let me know and I will try to provide when I get a chance.

    BTW excell advice from GL??! Isn't that a little dangerous??AstonishedSmiley

    Sorry had to make some sort of Scroll Lock ref.Smiley
    Attached Files
  • I want to avoid a spreadsheet with shiit all over the place, I'd rather not clutter things. I was able to use replace to fix the formulas instead of editing each one but my goal is to evaluate a name (set of data).

    Oh, sure, change the rules halfway through the game!Angry

    As far as not cluttering, you could always hide the column with the 1's and 0's.

    Thanks man FREQUENCY did it.

    Sounds like you got it.
  • Oh, sure, change the rules halfway through the game!Angry

    As far as not cluttering, you could always hide the column with the 1's and 0's.



    Sounds like you got it.

    Yeah, now he'll have to get the shots for it.
  • why not use a formula like:

    =COUNTIF(A1:A2000,">15")

    Assuming your data is in column A & you want to count how many numbers are greater then 15.
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement