• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Help: Cumulative Fail Rate Month x Month. Select a Value.

C

chloec

Member
Hi. I have two questions. Need formula help.

Need to figure out a formula for Cumulative Fail Rate Month by Month.


PASSED FAILED

Jan 0 0

Feb 0 0

Mar 0 3

Apr 3 1

May 5 6

Jun 2 0

Jul 4 0

Aug 4 0

Sep 21 0

Oct 3 0

Nov 12 1

Dec 6 0


Formula to accomplish the following:

1. Look at January - there were no passed test reports, so Fail rate is 0% - I never want Div/0 to appear

2. Look at February - there were no passed test reports, but I need the formula to add up Pass/Fails from Jan AND Feb and decide if it should be a 0

Look at March - there were 3 fail test reports, so Fail rate is 100% (3/0 = 100% fails). Need to add Data from Jan - Mar in both Fails and Passes to get Fail rate

3. Look at April - now there are 3 Pass test reports, so I need to take the sum of all previous months and find the fail rate


4. Look at May - now there are 8 Pass test reports, so I need to take the sum of all previous months and find the fail rate


5. Contine to get the cumulative fail rate for each month.

I used formulas like this - but I have no idea what I am doing:

Jan: =IF(AND(SUM($G$3>0),SUM($H$2=0)),"100%",SUM($G$3)/SUM($G$2:$G$3))

Dec: =IF(AND(SUM($G$3:$I$3>0),SUM($G$2:$J$2=0)),"100%",SUM($G$3:$I$3)/SUM($G$2:$J$2,SUM($G$3:$I$3)))


6 FAIL RATE: Sum the Total #s of Fails + The Total #'s of passes and divide by Fails - 60 + 11 = 71

11/60 = .18

--------------------------------


7 Need formula for YTD Performance Score: I want to look at the YTD Fail % as a number and pick the worst one (Unsatisfactory, Alert or Good)

Bring the worst of the two numbers to cell H13


Thank you very much for the assist. I have a spreadsheet I can upload. I don't know how to updload so heres a link to google docs (you should be able to download and edit): http://bit.ly/wTuqLw (links to google docs). I hope that no one will edit the document on google docs - just in case someone else wants to see it! Thanks again!!
 
Hi Chloe ,


Try the following formula in cell G6 :


=IF(SUM($G$2:G3)=0,0,SUM($G$3:G3)/SUM($G$2:G3))


Copy it to the remaining cells H6 through R6.


In cell H10 , for the YTD figure , use the following formula :


=OFFSET(G6,0,COUNT(G2:R2)-1)


Can you explain what numbers should appear in cells H11 and H12 , with reference to the data you have in your worksheet ?


Narayan
 
What a great help you are! I placed the suggested formulas in my workbook and they worked PERFECT! I can't believed you figured out what I needed amongst all my "chicken scratch".


H11 = "Grade" it's a # that I enter as a result of how responsive the student is.

H12 = I select the lowest of the two vales(H10 vs. H11) and bring it to cell H12. The lowest of the two grades is what the student needs to work the most on.


so - if grade falls between:

0-3.99 = GOOD is displayed

4 - 10.99 = ALERT is displayed

11+ = UNSATISFACTORY is displayed
 
Hi ,


Thanks for the clarification. I still have one doubt :


H11 is going to be entered by you ; it will be some number.


H10 contains the YTD fail rate ; this is also a number , between 0 and 1 ( though it is displayed as a percentage , between 0 and 100 ).


I assume you will enter H11 also as a number between 0 and 1.


H12 should contain the lower of these two values ; you can use the formula =MIN(H10,H11) to get this value ; note that if one of the cells H10 or H11 is blank , Excel will not take it as 0 ; instead , it will give the other value as the lower value.


Narayan
 
OK. Thank you kindly. For H11, yes, I enter in some number. I was trying to put in a value of 3.98 (for example) into celll H11 because I thought the student did a "good" job. But when I entered in 3.98 in H11, I saw unexpected results with the Min formula (=MIN didn't work correctly). Is it because H11 value must be between 0 - 1?


Thanks again!
 
Hi Chloe ,


Why do you say MIN doesn't give the correct result ?


For instance , if H10 = 15.5 % ( actually 0.155 ) , and if you enter 3.98 in H11 , then =MIN(H10,H11) will return 15.5 %


Narayan
 
Ok, you're correct. Can you please review my formula to see if the correct verbiage appears when using the MIN formula?

=IF(H56<3.99,"GOOD",IF(H56<10.99,"ALERT","UNSATISFACTORY"))


For example - the MIN formula correctly retrns 15.5% but text that is displayed should coorespond accordingly.


so - if grade falls between:

0-3.99 = GOOD is displayed

4 - 10.99 = ALERT is displayed

11+ = UNSATISFACTORY is displayed


You can see this in the spreadsheet. I10 - I12. Also, can you please share your personal email address using the spreadsheet or a throwaway address that you can post here? If you are allowed to do that.
 
Hi Chloe ,


Your usage of the IF function is correct. But where have you used the MIN function ?


Suppose the MIN formula returns 15.5 % ; the text that is displayed depends on the value you enter in H11 , or is it H56 now ?


My email address is narayank1026@gmail.com , but I can reply only tomorrow , since it is almost bedtime here !


Narayan
 
Oh yes, I didn't realize I did that. H11 = H56.If MIN formula returns 15.5% - I want "unsatisfactory" displayed - but it doesn't seeem to do that for me!
 
Hi. Sorry. =MIN function used in H12 and =IF function used in I12 of spreadsheet.


I want cell H12 to select the lowest of the values and then the I12 to display the cooresponding text (Good, Alert, Unsatisfactory) based on values previously provided.
 
I just can't wrap my head around the computation rationale that the failing rate is 11/60 and not 11/71...
 
Confirmed Fred. There's so many mistakes (by me) riddled throughout this ask. I tried to be as accurate as possible, but decided to change things at the last minute and forgot to update my figures here and there. Fail rate is 11/71. Thrilled that you and Narayan were able to follow along regardless!


I also think instead of using =MIM to get the area that needs most improvmement, I should have used =MAX instead.


The logic below has been refined (by me): Changed "Lowest" to "MAX" (?)

Does this even make sense?


H11 = "Grade" it's a # that I enter as a result of how responsive the student is.

H12 = I select the MAX of the two vales(H10 vs. H11) and bring it to cell H12. The MAX of the two grades is what the student needs to work the most on - given the values below.


so - if grade falls between:

0-3.99 = GOOD is displayed

4 - 10.99 = ALERT is displayed

11+ = UNSATISFACTORY is displayed
 
Here's an example of the =MAX and =IF that's not working for me.

FORMULAS USED ARE IN {BRACKETS}.

For Rankings: "Good, Unsatisfactory, Alert" I used formula:{=IF(H56<3.99,"GOOD",IF(H56<10.99,"ALERT","UNSATISFACTORY"))}

------Data------

PASSED TESTS 14

FAILED TESTS 4


YTD FAIL %: 22% UNSATISFACTORY {4/18}

GRADE: 2 GOOD {Manually entered}

YTD PERF. SCORE: 200% GOOD {=MAX(H54,H55}
 
Hi Chloe ,


Taking off from your last post , H54 is the YTD FAIL % , which is = 0.22 ( 22 % ). H55 is manually entered , and has the number 2. If you have put in the formula =MAX(H54,H55) in cell H56 , then H56 should have the value 2. If you have formatted H56 to show percentage , then it will display 200 % , otherwise , it will display 2.


Now , if in I54 , I55 and I56 , you have used the formulae :


I54 : =IF(H54<3.99,"GOOD",IF(H54<10.99,"ALERT","UNSATISFACTORY"))


I55 : =IF(H55<3.99,"GOOD",IF(H55<10.99,"ALERT","UNSATISFACTORY"))


I56 : =IF(H56<3.99,"GOOD",IF(H56<10.99,"ALERT","UNSATISFACTORY"))


then all three cells I54 , I55 , I56 should display GOOD.


However , when I go through your worksheet , against the YTD figure , you have used the following formula :


=IF(((H10*100)<3.99),"GOOD",IF(((H10*100)<10.99),"ALERT","UNSATISFACTORY"))


which means you are multiplying the YTD fail rate ( between 0 and 1 ) by 100 , to get a number between 0 and 100.


You need to decide whether the GRADE , a number between 0 and 11 ( any value greater than 11 is treated the same as 11 ) , is to be compared with the YTD fail rate or with the YTD fail rate multiplied by 100. If it is the latter , then the formula in H56 should be =MAX(H54*100,H55).


Otherwise all the formulae are correctly framed , and should work correctly.


Narayan
 
You must log in or register to reply here.
Back
Top