#Excel: Building a Frequency Summary Table Based on an Excel List by David Hager

After publishing a tip on the most frequent item in a list

https://dhexcel1.wordpress.com/2017/05/09/excel-short-and-sweep-tip-11-most-frequent-item-in-column-by-david-hager/

I decided to build a model based on frequency.

The first formula used is the aforementioned most frequent item formila.

=INDEX(ListRange,MODE(MATCH(ListRange,ListRange,0)))

where ListRange = A2:A16

Then, another formula was needed to return subsequent frequent items,

=INDEX(ListRange,MODE(IF(COUNTIF($E$2:E2,ListRange)=0,MATCH(ListRange,ListRange,0))))

entered into cell E3 and copied down.

The formula to return the number for each item was entered in F2 and copied down.

=COUNTIF(ListRange,E2)

The amount spent on each transaction type was calculated the formula placed in G2 and copied down.

=SUM(IF(ListRange=E2,Total,0))

where Total = B2:B16

Finally, information can be used columns F & G to make a formula that will return the average for each transaction type.

=ROUND(G2/F2,2)

The finished table is shown in the following figure.

 Freq1

The frequency table file can be downloaded here.

FreqItems

 

Advertisements

3 thoughts on “#Excel: Building a Frequency Summary Table Based on an Excel List by David Hager

  1. Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  2. Pingback: #Excel Super Links #50 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s