Using Count/Frequency/Binning to sort data

[Jarvice]

Hello All,

I have a large amount of data that is in two columns and I am trying to use some kind of formula to count the number of times a measurement falls between two values in each column and then use that data to populate a table. I've attached an example which shows a very simplified version of what I'm after.

In the example the X and the Y colums have the values and I want to populate the table on the right with how many samples with X = 1 also have Y =1 and how many have Y=2 etc. I've included my best shot so far but it isn't anywhere near what I'm after. The ultimate goal is to produce a surface plot and also analyse the count data in a statistical package (yet to be sourced).

Any help with this either through functions or coding would be much appreciated.

Thanks

Jarvice

[tigeravatar]

Jarvice,

It looks to me like you're trying to make it too complicated. A CountIfs() should work for this. In cell F3 and copied over and down (not an array formula):
=COUNTIFS(X,">="&$D3,X,"<"&$D3+1,Y,">="&F$2,Y,"<"&F$2+1)

[Special-K]

in E3
=SUMPRODUCT(--(INT($A$2:$A$20)=$D3)*($B$2:$B$20=F$2))

copy across and down

[Andrew-R]

In cell F9 of your example sheet I went with:

=SUMPRODUCT(--(X>=$D9),--(X<($D9+1)),--(Y=F$8))

Copied across and down - seems to match your expected output.

[Marcol]

Try this in F3

=SUMPRODUCT(--(X>=$D3),--(X<$D3+1),--(Y=F$2),--(Y<F$2+1))

Drag Across to Column J, then Down.

[Jarvice]

Hello All,

Thanks for the advice all. I went with Marcol's fix with a slight modification (substituted D$4 for D$3+1) to account for the fact that my X bins are different sizes. Works a treat and I can now plot the data as a 3D surface plot and replace an ancient MS-DOS programme that did the same thing! Now I just need to work out some statistics!

Thanks again

Gavin