MrExcel Publishing
Your One Stop for Excel Tips & Solutions

figuring occurrence


Posted by Sharon Calvert on July 03, 2001 1:11 PM

My columns are set up as such:

Div St# Emp Name TermDate Reason
CA 918 John Doe 3/16/01 Tardiness
CA 918 Jane Doe 2/25/01 None given
CA 922 Santa Claus 12/25/00 Toy delivery
CA 935 Mary Poppins 2/24/01 Child care probs

I would like figure the occurrence of turnover in stores; meaning, which store has the most amount of turnover and which store has the least amount
of turnover. I have over 200 stores I'm tracking and would like a nice,simple formula to figure these two areas.


Posted by Ben O. on July 03, 2001 1:22 PM

First of all I would get a list of unique store numbers and put them in column A of a new sheet.

In column B, next to each store, starting in row 2, enter this formula:

=SUM(IF(Terminations!$B$2:$B$250=A2,1,0))

It is an array formula so you must hold Ctrl and Shift when you press Enter.

The formula assumes the name of your main data sheet is "Terminations" and your data goes to row 250.

That formula will give you the sum of terminations for each store. You can use the MAX and MIN funtions to determine which store(s) have the most and least termination.

-Ben

Posted by lenze on July 03, 2001 2:14 PM

You could also use a Pivot Table Report