Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating % occurence of a value derived from data array
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating % occurence of a value derived from data array
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 "WildWill" wrote: 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!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating % occurence of a value derived from data array
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 "WildWill" wrote in message ... 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting data derived from formula | Excel Worksheet Functions | |||
Find first occurence of a number in an array 7 cols wide | Excel Worksheet Functions | |||
Counting Occurence of Criteria in Multiple Lists of Data | Excel Discussion (Misc queries) | |||
Identify occurence in "array" which meets condition(s) | Excel Discussion (Misc queries) | |||
First Occurence of Non Blank Cell in row array | Excel Worksheet Functions |