extract value based on header column

[Afarag]

Hello,

In my worksheet i have 2 Pivot tables with a different source, One of this pivots is filtered via slicer

i have 5 Groups

Business,Reapers,SA,Sales and WE
and there is a 4 common items for Each Group

Ecco, Wasla, Xceed and Staff

this groups and its items have a data for 7 months from Jan till Jul

the first table data is related to the headcount for every group item, but the second table data is related to the number of resigned employees,

i want to count the attrition for each group: "=(sum resigned/average headcount)"

the second table "Resigned" is connected vie slicer to filter Group and months,

i'll show the date for the group one by one

and if i select from month slicer eg: Jan, Feb and Mar for "Business", we will see that Business have one common item "Staff"

i need the result that extracted from this function but keep in mind that i'll change the group and months

Thanks a lot,

 

[Somendra Misra]

Hi Afarag,

Try below array formula in F25 and copy right.

=SUM(INDEX($B$15:$E$21,,MATCH(F23&F24,$B$13:$E$13&$B$14:$E$14,0)))/AVERAGE(OFFSET($B$4,MATCH($A$15:INDEX($A$15:$A$21,MATCH("zzzzz",$A$15:$A$21)),$A$4:$A$10,0)-1,MATCH(F23&F24,$B$2:$U$2&$B$3:$U$3,0)-1,1,1))

Enter with Ctrl+Shift+Enter.

Regards,

 

[Afarag]

Hi Somendra,
this above formula gives the right outcomes only if i select one month via slicer, but what a bout if i select more than one month.
to become more flexible; the pivots structure form isn't a must, and there isn't any problem in adding any helper column if needed

Thanks a lot,

 

[Somendra Misra]

@Afarag

As far as I have tested If I selected multiple values from slicer the results are changing, can you point out any error if there is in calculation.

Regards,

 

[Somendra Misra]

@Afarag

Try in F25 and copy right:

=SUM(INDEX($B$15:$E$21,,MATCH(F23&F24,$B$13:$E$13&$B$14:$E$14,0)))/AVERAGE(N(OFFSET($B$4,MATCH(monthSel,$A$4:$A$10,0)-1,MATCH(F23&F24,$B$2:$U$2&$B$3:$U$3,0)-1,1,1)))

Here monthsel is named range with formula
=Pivot!$A$15:INDEX(Pivot!$A$15:$A$21,MATCH("zzzzz",Pivot!$A$15:$A$21))

Enter with Ctrl+Shift+Enter.

Regards,

 

[Afarag]

@Somendra Misra

that's really the perfect solution that are provided

thanks a lot,

 

[Somendra Misra]

Thanks for the feedback. You are Welcome!!!

Regards,