1 Reply Latest reply: Feb 8, 2012 3:46 PM by bill_robbins777 RSS

    Count occurances of a value using greater than or lesser than...

      I need to count the number of times the values in a field named "Price Change" meets certain criteria.

       

      In my Price Change column, I need to count how many times:

       

      1. The price change is either + $0.01 or - $0.01 (positive one cent or negative one cent)

      2. The price change is either + $0.02 to +$0.10 or -$0.02 to -$0.10

      3. The price change is either + $0.11 to +$0.25 or -$0.11 to -$0.25

      4. The price change is either + $0.26 to +$0.50 or -$0.26 to -$0.50

      5. The price change is either + $0.51 to +$0.75 or -$0.51 to -$0.75

      6. The price change is either + $0.76 to +$1.00 or -$0.76 to -$1.00

       

      The simple version of the formula I have been using so far is:

       

       

      Count({< [Price Change] = {'0.01'} >} [Price Change])

       

      This doesn't really do anything like the whole job... Can someone help me out please?

       

      Thanks for any assistance.

       

      Regards,

       

      Bill.

        • Count occurances of a value using greater than or lesser than...

          I figured it out, and thought I'd share...

           

          The formula to get the count of values that are greater than 0, but less than or equal to 0.01 for my "Price Change" field is:

           

          Count({< [Price Change] = {'>0<=0.01'} >} [Price Change])

           

          The formula to get the count of values that are less than 0, but greater than or equal to -0.01 for my "Price Change" field is:

           

          Count({< [Price Change] = {'<0>=-0.01'} >} [Price Change])

           

          The formula to get the count of values that are greater than 0.01, but less than or equal to 0.10 for my "Price Change" field is:

           

          Count({< [Price Change] = {'>0.01<=0.10'} >} [Price Change])

           

          The formula to get the count of values that are less than 0.01, but greater than or equal to -0.10 for my "Price Change" field is:

           

          Count({< [Price Change] = {'<-0.01>=-0.10'} >} [Price Change])

           

          The other formulae are similar.  Just change the values to the range required and the operators in the middle of the formula to whatever you need.