age group

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

In general the following formula works for basic calculation :

between 20 to 29: =COUNTIF($D$2:$D$6566,">=20")-COUNTIF($D$2:$D
$6566,">30")
between 30 to 39: =COUNTIF($D$2:$D$6566,">=30")-COUNTIF($D$2:$D
$6566,">39")
...
old than 60: =COUNTIF($D$2:$D$6566,">=60")

One big problem is the HR data has more detail in age that althogh it
show 30, it can be 29.4 when you select the cell. Data like this (e.g.
39.6, 49.6) will not be covered in any group. How can I format the
numbers to be round-up integer numbers. If over .5 it goes to the next
number, if below .5, it takes .x away?

I have 2 other criteria (groups) I need to consider which I'm not sure
how to include:

Male vs Female,
Sales vs Service

Thanks for the help,
 
C2 = 20
D2 = 29
E2 = gender
F2 = dept

=SUMPRODUCT(--(ROUND(range1,0)>=C2),--(ROUND(range1,0)<=D2))

=SUMPRODUCT(--(ROUND(range1,0)>=C2),--(ROUND(range1,0)<=D2),--(range2=E2))

=SUMPRODUCT(--(ROUND(range1,0)>=C2),--(ROUND(range1,0)<=D2),--(range2=E2),--(range3=F2))
 
You're welcome. Thanks for the feedback!
Click to expand...
How about average using the same department, gender criteria? I tried
something like

=IF($B$2:$B$6566="Sales",AVERAGE($D$2:$D$6566),"")

but it came up with the same average of all.

Thanks,
 
How about:

=AVERAGE(IF($B$2:$B$6566="Sales",$D$2:$D$6566))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Or this normally entered formula:

=sumif($B$2:$B$6566,"Sales",$D$2:$D$6566)/countif($B$2:$B$6566,"Sales")

To avoid the divide by 0 error if there are no Sales in that range:

=if(countif($b$2:$b$6566,"Sales")=0,"No Sales",
sumif($B$2:$B$6566,"Sales",$D$2:$D$6566)/countif($B$2:$B$6566,"Sales"))
 
Hi,

First going back to your original question, I prefer SUMPRODUCT but

=COUNTIF($D$2:$D$6566,">=19.5")-COUNTIF($D$2:$D$6566,">30.5")

If you are working with all three critieria at the same time then again
SUMPRODUCT:

=SUMPRODUCT(--(B2:B6566="Male"),--(C2:C6566="Sales"),--(D2:D6566>=19.5),--(D2:D6566<29.5))

for average

=SUMPRODUCT(--(B2:B6566="Male"),--(C2:C6566="Sales"),--(D2:D6566>=19.5),--(D2:D6566<29.5),D2:D6566)/SUMPRODUCT(--(B2:B6566="Male"),--(C2:C6566="Sales"),--(D2:D6566>=19.5),--(D2:D6566<29.5))

Or if you are using 2007:

=COUNTIFS(B2:B6566,"Male",C2:C6566,"Sales",D2:D6566,">=19.5",D2:D6566,"<29.5")
and
=AVERAGEIFS(D2:D6566,B2:B6566,"Male",C2:C6566,"Sales",D2:D6566,">=19.5",D2:D6566,"<29.5")

Ofcourse you can modify the ">19.5" to reference cells or use the ROUND
function within them if you want.
 
Back
Top