Headcount

[sonu1975]

Hi,

I want to calculate headcounts in the file and the problem is where there is same employee name more than once which will give me wrong nr of heads.
I want a formula which puts an "1" in the cell which has the highest rate of FTE and "0" on other duplicate ones with lower FTE rate. I have written it worn in the file.

Thanks for all help !

[torachan]

Please check spelling - is it Enployee OR Employee - makes a difference to the result

[sonu1975]

Sorry, Employee

[Glenn Kennedy]

1. Enployee 1 and Employee 1 are not the same. Ensure consistency.

2. Use this, copied down:
=IF(E2=AGGREGATE(14,6,$E$2:$E$13/($A$2:$A$13=A2),1),1,"")

3. You may need ; as the separator.

[sonu1975]

Thanks a lot !

BTW
What does "1" in [k] criteria refers to ? (trying to understand the formula)

IF(E2=AGGREGATE(14;6;$E$2:$E$13/($A$2:$A$13=A2);1);1;"")

[Glenn Kennedy]

14 in aggregate means large. The 1 means return the 1st (largest) value.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[sonu1975]

Thanks for the explanation and for the info, cheers !

[sonu1975]

Hi again,

Having some issues with multiple criteria in formula "Aggregate".

In the example file sheet "Data" column "M" I have written the aggregate formula (help from Glenn Kennedy) to give me the value of 1 (as 1 head) on those which have duplicate names.
This works it the number in column "H" is positive and as one criteria.
I found out now that I need multiple criteria in the same formula.

Additional criterias:
1. If Emp Rate (column E) is not 100 then Heads (column M) should show 0
2. If Emp Rate (column E) is 100 then match value from Salary (column C) from sheet "Definition" column "D" and give a value. (Example can be seen in row 1265)
Basically the column M should reflect column I in sheet "Data".

How to achieve this ?