On Tue, 19 Mar 2013 17:22:21 +0000, EmzOLV wrote:


Hey guys! Hope you can help me - I've been struggling trying to work out
what is happening with some of my calculations.

Here is my scenario:
I am trying to identify, between a range of cells (in this case, P5:P185
on the separate worksheet RED WET 1), how many of these contain a value
less than 5, a value between 5 and 10, a value between 10 and 20, and
upwards up until 100, whereby I then want to work out how many cells
contain a value of 100 or more.


For a problem like this, I would use the FREQUENCY function.

=FREQUENCY(P5:P185,{5,10,20,100})

Enter it as an array from, for example A1:A5
As written,
A1: will show the count of values less than or equal to five.
A2: will show the count of values greater than five but less than or equal to ten
A3: will show the count of values greater than ten but less than or equal to 20.
A4: will show the count of values greater than 20 but less than or equal to 100
A5: will show the count of values greater than 100

To enter the formula, first select A1:A5. Enter the formula into the formula bar, then, instead of hitting <ENTER, hold down <CTRL+SHIFT while hitting <ENTER. Excel will copy the same formula into A1:A5 and place curly brackets {...} around the formula.