Running Frequency Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

(I tried to post this last week, but the post seems to be missing; I'm sorry
if it's the second time you've come across it)

I've developed a histogram for a small dataset (132 records) using the
FREQUENCY function. However, because the data was compiled at intervals of
time, I've wondered how the bins have been filled since the data began to be
collected.

So, I'd like to create a table that looks like this:

11 12 23 45 56 <--bins
Date1 1 0 2 1 1
Date2 1 0 3 1 1
Date3 2 1 3 1 1
Date4 2 2 3 2 1
Date5 2 2 3 2 2

....and so forth.

The problem that I'm having is that FREQUENCY must be dragged downwards. Is
there a way for it to be used as I want it to be?

Thanks yet again!
 
Assumptions:

Source table...

A2:A10 contains the date

B2:B10 contains the corresponding numbers

Results table...

E2:E6 contains a list of unique dates

F1:J1 contains the bins

Formula:

F2, copied down and across:

=INDEX(FREQUENCY(IF($A$2:$A$10=$E2,$B$2:$B$10),$F$1:$J$1),COLUMNS($F2:F2)
)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Once again, you've helped me out immensely.

However, I decided that I didn't need another date column, and embedded this
running freq table in my dataset, essentially (which already had the date ID
column in in). That gave me the following formula, which appears to work the
way intended:

Data = C3:G135
Bins = A1218:A1222 (I had difficulty getting the bins recognized
horizontally; probably my own bumbling)

New Table from HC3:HG135

=INDEX(FREQENCY($C$3:$G3,$A$1218:$A$1222),COLUMNS($HC3:HC3))

(I removed the dollar sign off of the "$G$3 to get a running frequency when
I copied the formula down)

Thanks so much!
 
Back
Top