Getting percentage from columns and raws, divide by 0 error...

**masta-s** · 08-08-2013, 01:50 PM

Hello Ladies and Gents!

Today People on this forum have already helped me hugely, but I have one more small propblem which I hope you help me to solve:

I have a kind of training matrix. Let's say columns have different topics, and rows have people names.

Each topic consists of three columns - Required (Yes/No) | Date of expiry| Validity (VALID/NOT VALID)

I basically need two formulas and I hope I have something to start with:

- one which will get me percentage of compliance of certain training along all people (vertical range)

=COUNTIFS(D5:D11,"VALID",B5:B11,"Yes")/COUNTIF(B5:B11,"Yes")

-another one - compliance of people by all trainings (horizontal range)

=COUNTIFS(A7:L7,"Yes",A7:L7,"VALID")/COUNTIF(A7:L7,"Yes")

The problem is that when All people in a certain group don't require specific training, the formula returns me "divide by zero error", because non of them has "Yes" in the requirement...

Another problem is that when I try to get percentage horizontally (Formula #2), it returns me 0, although I've tested it on one man with two topics, both of them were required, and both of them valid, but the result I got was 0...

I hope you can help me to solve these problems...

Thanks in advance!

**Pete_UK** · 08-08-2013, 06:50 PM

You need to do something like this with your first formula:

=IF(COUNTIF(B5:B11,"Yes")=0,0,COUNTIFS(D5:D11,"VALID",B5:B11,"Yes")/COUNTIF(B5:B11,"Yes"))

to avoid the DIV!0 error.

The second one needs a similar adjustment, but note that I have also adjusted the range for the second term in the COUNTIFS - it needs to be two columns later, going by your first formula:

=IF(COUNTIF(A7:L7,"Yes")=0,0,COUNTIFS(A7:L7,"Yes",C7:N7,"VALID")/COUNTIF(A7:L7,"Yes"))

Hope this helps.

Pete

**masta-s** · 08-09-2013, 01:33 AM

Hi Pete!

Thanks, the first formula is fixed now, but I changed 0 to 1 in the formula:

=IF(COUNTIF(B5:B11,"Yes")=0,0,COUNTIFS(D5:D11,"VALID",B5:B11,"Yes")/COUNTIF(B5:B11,"Yes"))

Because even if all people don't require the training, the compliance still should be 100%.

Now the problem left only with the second formula... You see, like every row consists of name and then Training 1(Required(Yes/No), Date of Expiry, Validity(VALID / NOT VALID)), Training 2(Required(Yes/No), Date of Expiry, Validity(VALID / NOT VALID))... and so on. So I need to count how many times the word "yes" is met along with the word "VALID" and divide it by the amount of words "Yes"...
For example, if John has 2 training, one of them Required-Yes and Validity-VALID, and another training is Required-Yes but Validity-NOT VALID, I need the formula to count: 1:2 (because only 1 training is required and valid at the same time and divide it by 2 required trainings....

Update: Hmm, basically that works but I don't get why you need to change the range in that formula... Why you need to change the range by shifting to the right exactly by 2 cells in this formula you provided:

=IF(COUNTIF(A7:L7,"Yes")=0,0,COUNTIFS(A7:L7,"Yes",C7:N7,"VALID")/COUNTIF(A7:L7,"Yes"))

I tried to shift it one cell left or right but it gives me a wrong answer.... What is a secret? :-)

**Pete_UK** · 08-09-2013, 04:44 AM

From your first formula you were looking for "yes" in column B and "valid" in column D, so they are 2 cells apart, and I presume this pattern is repeated across the rows. So, at the time that you are looking in column A for "yes", you also want to be looking in column C for "valid", then B/D, then C/E, then D/F and so on. That is why I offset the second range by two cells.

Hope this helps.

Pete