# Thread: Division by zero problem

1. ## Division by zero problem

Hi Experts,

I am dealing with a difficult problem, at least for me. I have more hours in trying to solve this problem than I care to admit.

Here is what I am dealing with:

In column P2 to P1000 I have 0 and 1's. O means incorrect and 1 means correct. In column Q 2 to Q1000 I have: =IF(P2=1,"Correct",IF(P2="","","Incorrect")).

In cell R1 I have the number of Correct's, in S1 I have the number of Incorrect's. In cell T1 I have the total of Corrects and Incorrects. In cell U1 I have: =IFERROR(R1/T1,"")*100. This provides a percentage. All of this provides a score. For example, if the user gets 5 out of 10 answers correct the form I am working with
will show 50 Percent.

The problem I am having is when I close the form I have code (below) that clears column P of 1's and 0's. This causes cell U1 to attempt to divide by 0.

Range("P2", Columns("P").SpecialCells(xlCellTypeLastCell)).Cle ar

I use this code so if the user skips a line the above code will clear everything in Column P and column Q. This makes a mess. So I tried using the following code to replace the If function used in column Q with a command button to see the score. It works part of the time. That isn't good enough.

Private Sub SeeScore_Click()
Dim x As Long

x = Range("Q" & Rows.Count).End(xlUp).Row
If x < 1 Then x = 1
Range("R1") = Application.WorksheetFunction.CountIf(Range("Q2:Q" & x), "correct")
Range("S1") = Application.WorksheetFunction.CountIf(Range("Q2:Q" & x), "incorrect")

Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=1,""Correct"",IF(RC[-1]="""","""",""Incorrect""))"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q100"), Type:=xlFillDefault
Range("Q2:Q100").Select

Me.Percentage.Caption = Format(Sheet1.Range("U1").Value, "00")
Me.LblPercent.Visible = True

I hope this explanation is clear enough for experts to ID the problem.

Thanks

2. E.N.,

First you can replace: =IF(P2=1,"Correct",IF(P2="","","Incorrect"))
With: =IF(P1,"Corrrect","Incorrect")

The above works because in Excel 0 is false and anything else is true, strange but TRUE!
ExcelTruth.PNG

To fix you Div by Zero replace: =IFERROR(R1/T1,"")*100
With: =IFERROR((R1/T1)*100,"")
DIvbyzero.PNG

HTH

3. Hi RG

The above works because in Excel 0 is false and anything else is true
..as long as 'anything else' is numeric.
so I would use
=IF(n(P1),"Corrrect","Incorrect")
..just in case column A had a 'fred'

zeddy

zeddy

4. ## The Following User Says Thank You to zeddy For This Useful Post:

RetiredGeek (2016-01-27)

5. just in case column A had a 'fred'
why would I be in col A?

Fred

6. Zeddy,

Nice touch!

7. Originally Posted by fburg
why would I be in col A?

Fred
Because column A is the best column. Obviously!

My voice-activated-automatic-typer is going a bit death and is having truffle with my accent.

zeddy

9. Experts:

I prepared a mock up file to show the problems I'm having.

In short, this is what is creating challenges for what I thought would be easy.

1. When I use this code: =IF(P1,"Correct","Incorrect")
it fills in all the blanks cells with Incorrect and messes up the scoring system.

2. When I close the form, I've tried three different lines of code to clear or delete the existing scores so the next user can start with a new score.

Two problems develop:

1. One line of code doesn't clear blank cells thus making problems with the scoring system
2. The line of code that clears blank cells somehow ends up deleting everything in the adjoining column as well.

Please check out the attached file so you can clearly see what I've described.

Thanks for all that you do!

10. Excel,

Use the line:
Range("P2:P" & ActiveSheet.Cells(Rows.Count, "P").End(xlUp).Row).Clear

11. ## The Following User Says Thank You to Maudibe For This Useful Post:

Excelnewbie (2016-01-27)

12. Maudibe,

That solved the problem!! Thank you for providing the solution to this frustrating puzzle.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•