Formula for Changes in Head Count

Hi everyone,

First of what will likely be many posts I will make on this forum. Hope to provide a helping hand to others as well.

My current predicament involves trying to fashion a formula for a somewhat simple but repetitive task I will continually be asked to do.

I must make note of any changes to forecasts for a 10 month period, in terms of head count.


a.) each row is seperated by the individual employee being forecast for.

b.) the columns are 1 thru 10, each representing a month in sequence beginning with September 2012

c.) the values in this table are either 1 (if they were present for that month) or 0 (if they were not present during that month)

d.) All I want to find out is whether an employee was added sometime during this forecast/ or terminated during this forecast of 10 months or both.

e.) employees will only be hired and/or fired once during this period.

f.) If a person is both hired and then terminated in this period, it will always occur that they are hired first followed by the termination.



I have written logic that I believe takes into consideration all of the above, down below:

1.) If ( 0 < SUM A1:G1 < 10 ) and if(G1=1), then insert the number 1 (representing one addition during the period).
2.) If ( 0 < SUM A1:G1 < 10 ) and if(G1=0) and if (A1=0), then insert both the number 1 for an addition and -1 for a termination.
3.) If ( 0 < SUM A1:G1 < 10 ) and if(G1=0) and if (A1=1), then insert -1 for a termination.


The problem is I am not sure how to put the above logic into a formula format in excel.

Any and all help would be appreciated.

Try putting this in a cell.

Formula:

Please Login or Register  to view this content.

Edit: Sorry didn't read properly, this was just the first row.

Second try. This does what you want assuming the -1 and the 1 cancels out and becomes zero.

Formula:

Please Login or Register  to view this content.