Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Smile 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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    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. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    just in case column A had a 'fred'
    why would I be in col A?

    Fred

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Nice touch!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    557
    Thanks
    51
    Thanked 68 Times in 66 Posts
    Quote Originally Posted by fburg View Post
    why would I be in col A?

    Fred
    Because column A is the best column. Obviously!


  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    ..it was another thread I was thinking about.
    My voice-activated-automatic-typer is going a bit death and is having truffle with my accent.

    zeddy

  9. #8
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Talking

    Experts:

    This is a fun thread!

    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!
    Attached Files Attached Files

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    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. #10
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    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
  •