Excel: Creating a question to add 2 mixed fractions but the input answer is almost never right thoug-CodePudding

This is my problem:

This image tells the outlook I intend to move forward with:

But following is the expanded form of the same:

How to solve this problem?

(Note 1: "Verify" is red as conditional formatting is set to check if value in "Answer" and "Verify" cell are same or not, if not, the "Verify" cell turns red as in pic.)

(Note2: Do not concern how "Verify" verifies despite conditional formatting because I am able to do it, so assume that "Verify" is able to tell me if I'm wrong or right, but the problem is the value it stores.)

CodePudding user response：

You can use my function ParseFeetInches to convert the expressions to Decimal which holds a much larger precision. Your example data will not require rounding, but if you had two fractions you might - as here:

Frac1 = ParseFeetInches("7 1/6")
Frac2 = ParseFeetInches("8 1/6")
Verify = ParseFeetInches("15 1/3")

? Frac1
 7,1666666666666666666666666667 
? Frac2
 8,166666666666666666666666667 
? Frac1   Frac2
 15,333333333333333333333333334 
? Verify
 15,333333333333333333333333333 

? RoundMid((Frac1   Frac2), 9) = RoundMid(Verify, 9)
True

The parsing function:

' Parse a string for a value of feet and/or inches.
' The inch part can contain a fraction or be decimal.
' Returns the parsed values as decimal inches.
' For unparsable expressions, zero is returned.
'
' Maximum returned value is  /- 7922816299999618530273437599.
' Negative values will only be read as such, if the first
' non-space character is a minus sign.
'
' Smallest reliably parsed value is the fraction 1/2097152
' or the decimal value 0.000000476837158203125.
'
' Requires when not used in Access, for example Excel,
' either:
'   Module Access
' or a reference to Access, for example for Access 2016:
'   Microsoft Access 16.0 Object Library
'
' 2018-04-19. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ParseFeetInches( _
    ByVal Expression As String) _
    As Variant
    
    Dim ReplaceSets(20, 1)  As String
    Dim ExpressionParts     As Variant
    Dim ExpressionOneParts  As Variant

    Dim Sign                As Variant
    Dim DecimalInteger      As Variant
    Dim DecimalFraction     As Variant
    Dim DecimalInches       As Variant
    Dim Index               As Integer
    Dim Character           As String
    Dim FeetInches          As String
    Dim ExpressionOne       As String
    Dim ExpressionOneOne    As String
    Dim ExpressionOneTwo    As String
    Dim ExpressionTwo       As String
    Dim Numerator           As Long
    Dim Denominator         As Long

    ' Read sign.
    Sign = Sgn(Val(Expression))
    ' Trim double spacing.
    While InStr(Expression, "  ") > 0
        Expression = Replace(Expression, "  ", " ")
    Wend
    ' Replace foot units.
    ReplaceSets(0, 0) = "feet"
    ReplaceSets(0, 1) = "'"
    ReplaceSets(1, 0) = "foot"
    ReplaceSets(1, 1) = "'"
    ReplaceSets(2, 0) = "ft."
    ReplaceSets(2, 1) = "'"
    ReplaceSets(3, 0) = "ft"
    ReplaceSets(3, 1) = "'"
    ReplaceSets(4, 0) = Chr(SmartSingleQuote)   ' Smart Quote: "’"
    ReplaceSets(4, 1) = "'"
    ReplaceSets(5, 0) = " '"
    ReplaceSets(5, 1) = "'"
    ' Replace inch units.
    ReplaceSets(6, 0) = "inches"
    ReplaceSets(6, 1) = """"
    ReplaceSets(7, 0) = "inch."
    ReplaceSets(7, 1) = """"
    ReplaceSets(8, 0) = "inch"
    ReplaceSets(8, 1) = """"
    ReplaceSets(9, 0) = "in."
    ReplaceSets(9, 1) = """"
    ReplaceSets(10, 0) = "in"
    ReplaceSets(10, 1) = """"
    ReplaceSets(11, 0) = Chr(SmartDoubleQuote)  ' Smart Quote: "”"
    ReplaceSets(11, 1) = """"
    ReplaceSets(12, 0) = "''"
    ReplaceSets(12, 1) = """"
    ' Replace decimal separator.
    ReplaceSets(13, 0) = ","
    ReplaceSets(13, 1) = "."
    ' Replace units with operators.
    ReplaceSets(14, 0) = """"
    ReplaceSets(14, 1) = ""
    ReplaceSets(15, 0) = "'"
    ReplaceSets(15, 1) = "*" & CStr(InchesPerFoot) & " "
    ' Remove divider spaces.
    ReplaceSets(16, 0) = " /"
    ReplaceSets(16, 1) = "/"
    ReplaceSets(17, 0) = "/ "
    ReplaceSets(17, 1) = "/"
    ' Replace disturbing characters with neutral operator.
    ReplaceSets(18, 0) = " "
    ReplaceSets(18, 1) = " "
    ReplaceSets(19, 0) = "-"
    ReplaceSets(19, 1) = " "
    ReplaceSets(20, 0) = " "
    ReplaceSets(20, 1) = " 0"
    
    ' Add leading neutral operator.
    Expression = " 0" & Expression
    ' Apply all replace sets.
    For Index = LBound(ReplaceSets, 1) To UBound(ReplaceSets, 1)
        Expression = Replace(Expression, ReplaceSets(Index, 0), ReplaceSets(Index, 1))
    Next
    ' Remove any useless or disturbing character.
    For Index = 1 To Len(Expression)
        Character = Mid(Expression, Index, 1)
        Select Case Character
            Case "0" To "9", "/", " ", "*", "."
                FeetInches = FeetInches & Character
        End Select
    Next
        
    ' For unparsable expressions, return 0.
    On Error GoTo Err_ParseFeetInches
    
    ExpressionParts = Split(FeetInches, "/")
    If UBound(ExpressionParts) = 0 Then
        ' FeetInches holds an integer part only, for example, " 00 038*12 0 05".
        ' Evaluate the cleaned expression as is.
        DecimalInches = Sign * CDec(Eval(FeetInches))
    Else
        ' FeetInches holds, for example, " 00 038*12 0 05 03/2048 0".
        ' For a maximum of decimals, split it into two parts:
        '   ExpressionOne = " 00 038*12 0 05 03"
        '   ExpressionTwo = "2048 0"
        ' or Eval would perform the calculation using Double only.
        ExpressionOne = ExpressionParts(0)
        ExpressionTwo = ExpressionParts(1)
        ' Split ExpressionOne into the integer part and the numerator part.
        ExpressionOneParts = Split(StrReverse(ExpressionOne), " ", 2)
        ' Retrieve the integer part and the numerator part.
        '   ExpressionOneOne = " 00 038*12 0 05"
        '   ExpressionOneTwo = "03"
        ExpressionOneOne = StrReverse(ExpressionOneParts(1))
        ExpressionOneTwo = StrReverse(ExpressionOneParts(0))
        
        ' Extract numerator and denominator.
        If Trim(ExpressionOneOne) = "" Then
            ' No integer expression is present.
            ' Use zero.
            ExpressionOneOne = "0"
        End If
        Numerator = Val(ExpressionOneTwo)
        Denominator = Val(ExpressionTwo)
        
        ' Evaluate the cleaned expression for the integer part.
        DecimalInteger = CDec(Eval(ExpressionOneOne))
        ' Calculate the fraction using CDec to obtain a maximum of decimals.
        If Denominator = 0 Then
            ' Cannot divide by zero.
            ' Return zero.
            DecimalFraction = CDec(0)
        Else
            DecimalFraction = CDec(Numerator) / CDec(Denominator)
        End If
        ' Sum and sign the integer part and the fraction part.
        DecimalInches = Sign * (DecimalInteger   DecimalFraction)
    End If
    
Exit_ParseFeetInches:
    ParseFeetInches = DecimalInches
    Exit Function
    
Err_ParseFeetInches:
    ' Ignore error and return zero.
    DecimalInches = CDec(0)
    Resume Exit_ParseFeetInches
    
End Function

Full code (including RoundMid and other high precision rounding functions) and documentation can be found at my project VBA.Round.

So try:

=RoundMid(ParseFeetInches([CellFrac1])   ParseFeetInches([CellFrac2]), 9) 
and:
=RoundMid(ParseFeetInches([CellTotal], 9)

or:
=(RoundMid(ParseFeetInches([CellFrac1])   ParseFeetInches([CellFrac2]), 9) = RoundMid(ParseFeetInches([CellTotal], 9))