Need to calculate percentage of an occurrence

[sm370]

I have a set of data in which there are values of 0's and 1's. I'm trying to determine what the percentage of occurrence is when a 1 immediately follows a zero. I've attached an example of the data. Any and all help is greatly appreciated.

[AliGW]

You will need to tell us what your expected outcome for that set of data is and why.

[sm370]

This set of data is part of a larger data set in which the values of 0 and 1 represent a loss or a win for sales team performance. I'm trying to determine what the percentage of losses are followed by a win.

[AliGW]

Yes, I understand that. So, based on the sample data you've given us, what are you expecting that percentage to be?

[canapone]

Hi All,

I don't know which denominator could be the correct one

maybe

=COUNTIFS($A$1:$A$39,1,A$2:A$40,0)/COUNTA($A1:$A40)

or

=COUNTIFS($A$1:$A$39,1,A$2:A$40,0)/COUNTIFS($A$1:$A$40,1)

Regards

[sm370]

Thank you canapone, I believe the first formula is correct. Now my next issue, is that I am running this report daily and have two separate worksheets that I'm using. One as the data and two for analysis. My data sheet has over 150K's and grows daily. My employee's are listed in columns A on both sheets and the data being analyzed with the formula above in in column G on sheet "DALL". So my formula's live on sheet "TOP" and my data is on sheet "DALL" with row 1 in both sheets being used as headers, hence the reason all start on row 2. With that said, here is the formula I am using, but still is not working. I'm not extremely knowledgeable with regards to formulas so again thank you for any and all assistance.

=COUNTIFS(DALL!$A$2:$A$299999,TOP!$A2,DALL!$G$2:$G$299998,1,DALL!$G$2:$G$299999,0)/COUNTA(DALL!$A$2:$A$299999,TOP!$A2,DALL!$G$2:$G$299999)

[canapone]

Hi,

a question: why are you matching the content of A2?


Ranges should have same extension

=COUNTIFS(DALL!$A$2:$A$299998,TOP!$A2,DALL!$G$2:$G$299998,1,DALL!$G$3:$G$299999,0)/COUNTA(DALL!$A$2:$A$299999,TOP!$A2,DALL!$G$2:$G$299999)

Top!A2 cannot be compared in COUNTA, maybe denominator could be adjusted this way


=COUNTIFS(DALL!$A$2:$A$299998,TOP!$A2,DALL!$G$2:$G$299998,1,DALL!$G$2:$G$299999,0)/(COuntif(DALL!$A$2:$A$299999,TOP!$A2)+COUNTA(DALL!$G$2:$G$299999))


A small sample file would be helpful.


Regards

[sm370]

Perhaps I was wrong in my initial explanation and anticipated outcome. Attached is my workbook being used, without all of the irrelevant data and formulas. What I'm trying to determine is what is the percentage of losses followed by wins. For example:
Employee: 21popolig
Number of records: 504
Total Wins: 251
Total Losses: 242
Total undetermined (blank): 11

Of the 242 losses, 126 are followed by a win; which would put the percentage of wins after loss at 52%? This did not include losses followed by (blank) followed by a win (which totaled 3).
Using the formula below and only the data pertaining to employee "21popolig" on sheet "21popolig", I got the same answer... So my question is how can I convert this formula into sheet "Top"?

=COUNTIFS($A$1:$A$503,1,A$2:A$504,0)/COUNTIFS($A$1:$A$504,0)

[canapone]

Hi

first number should be 131

=SUMPRODUCT(--(DALL!$A$2:$A$42095&DALL!$C$2:$C$42095&DALL!C$3:$C$42096=A2&"01"))


Hope it's a little step forward.