• 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.

Dividing by zero...

D

DWSKAMS

New Member
I track inspections (passes and fails) and calculate the pass rate based on (total inspections)-(fails)/(total inspections) and get a pass rate percentage.


When there aren't any inspections at all and a 0 is entered into the (total inspections) cell the result of the formula shows as #DIV/0!


How can I get my formula to show a 100% pass rate when there are 0 inspections?
 
Hi ,


Use an IF statement as follows :


=IF(total inspections=0,100%,(total inspections-fails)/total inspections)


Narayan
 
The IF statement works great until I try to create a bar chart (4 different groups)showing the pass rate and there is a 0 in the (total inspections)cell. The chart shows 0%.


How do I get the chart to show 100%? Is there any IF statement I can use for that?


Also, is there a way to conditionally format the different bars on the chart to show a different color depending on the pass rate?


Thanks again for the help.
 
Hi ,


Sorry , but I am not able to visualise how your data is laid out ; however , if you are directly charting inspections , then when a cell has 0 in it , the chart will reflect that.


If you can introduce a helper column / row , where it looks at the inspections numbers , and if any one is 0 , puts 100 , then this helper column / row can be used in your chart instead of the actual inspections column / row.


If you want each bar to have a different colour , or even a different colour based on the pass rate , I think you will have to have 4 different series , with each series of a different colour. Each series will have one value , and NA() in the remaining so that one bar represents just one value.


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