Greater Than, Less Than Formula Needed

[JAgger1]

Hi All

I have 15000 voltage data points in a spreadsheet. The data points
ranging from .001 volts to 9 volts. I need to get the count for each
voltage point that is between 0-1, 1-2, 2-3, 3-4 etc. How would I
write the formula to get this information? Thanks in advance.

 

[Pete_UK]

Assume your voltages are in columnA. Set up a little table somewhere,
say M1:N10, and put 0 in M1, 1 in M2, 2 in M3 etc, down to M10.

Then put this formula in N2:

=sumproduct((A$1:A$15000<M2)*(A$1:A$15000>=M1))

Then you can copy this formula into N3:N10 to give you a count in each
range. Note the first count is for less than 1 (i.e. up to 0.999), the
second count is for less than 2 (up to 1.999) etc.

Hope this helps.

Pete

 

[Gord Dibben]

Have a look at the FREQUENCY function.

Assume data range is A1:A15000

You will need a BINS range of 1 to 9 in B1:B9

Do not forget to select C1:C9 before entering the formula in C1

Also note this is an array formula................ctrl + shift + enter


Gord Dibben MS Excel MVP