Average Outlets/Site Outliers

[Basavaraj K H]

Hi


Kindly Refer the attachment file.

I Required formula in the L5, L6 & L7 as per header i.e., (Average Outlets/Site Outliers).

Details are mentioned in the the next sheet "Details- Jan" take only from R & S column.

Please help me.

Thanks in Advance


Regards,

Basavaraj K H

 

[Somendra Misra]

Hi Basavraj,

Will there be seperate sheets for each months? and if so will the name will remain standards like Details -Jan, Details - Feb and so on...?

Regards,

 

[Basavaraj K H]

Kindly provide me only Jan month Details.

 

[Somendra Misra]

See the attached file.

Regards,

 

[Basavaraj K H]

Sorry, I'am not get answer

I want the Average of Transacting Outlets / (divided by) Slab of Transacting Outlets (Count) , i.e., three slabs "0", "1-3" & "4-7".
How to write formula please help me.

Thanks
Basavaraj K H

 

[Somendra Misra]

I thought the side table is the required answer.

Can you demonstrate one sample.

regards,

 

[Somendra Misra]

Try in L5 and copy down:

=SUMPRODUCT((LOOKUP("zzzzzz",$J$5:$J5)='Details- Jan'!$R$1:$AC$1)*(INDEX('Details- Jan'!$R$2:$AC$517,,MATCH("Slab "&LOOKUP("zzzz",$J$5:$J5),'Details- Jan'!$R$1:$AC$1,0))='Norms & Outliers'!$K5)*(INDEX('Details- Jan'!$R$2:$AC$517,,MATCH(LOOKUP("zzzz",$J$5:$J5),'Details- Jan'!$R$1:$AC$1,0))))/SUMPRODUCT((LOOKUP("zzzzzz",$J$5:$J5)='Details- Jan'!$R$1:$AC$1)*(INDEX('Details- Jan'!$R$2:$AC$517,,MATCH("Slab "&LOOKUP("zzzz",$J$5:$J5),'Details- Jan'!$R$1:$AC$1,0))='Norms & Outliers'!$K5))

Regards,