Calculating % occurence of a value derived from data array

[WildWill]

Hi

I have the following data-set:

A B
20 Absconded
21 Voluntary
22 Voluntary
23 Voluntary
23 Absconded
23 Voluntary
27 Voluntary
27 Voluntary
27 Absconded
27 Voluntary

Where Column A = Week Numbers of a Calender Year & Column B is Reasons for
Service Terminations.

I need to report as follows:

Week 22:
Absconded = 0%
Voluntary = 100%

Week 23:
Absconded = 33%
Voluntary = 66%

Week 27:
Absconded = 25%
Voluntary = 75%

I need a formula that will calculate the % as indicated above, from the data
set provided. Thanks! Awesome Forum!!!

 

[Teethless mama]

C1: holds week number
C2 and C3 hold type of Service Termination


In D1:
=SUMPRODUCT(--($A$1:$A$10=$C$1),--($B$1:$B$10=C2))/COUNTIF($A$1:$A$10,$C$1)

copy down

 

[Ashish Mathur]

Hi,

you may also use the following array formula (Ctrl+Shift+Enter)

SUM(IF(($A$1:$A$10=A$12)*($B$1:$B$10=A13),1))/COUNTIF($A$1:$A$10,A$12)


A12 has 22 and A13 has Absconded
--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com