0

Problem:

enter image description here

I typed my answer as 17 8/15 (as mixed fraction that is) in the answer box but the answer became 17 ½ though that’s not a problem as the decimal value in both the cells is same and that of 17 8/15.

enter image description here

This (as shown in the pic above) is the value in the formula bar of the Answer box (this is same as that of 17 8/15).

(Note: Further when I copy and paste as values the contents of the above 2 cells, they are as follows:

enter image description here

which means they do have the same values in them.)

But while I type my answer in the answer box, a function works in the Verify Box.

The function is as follows:

=IF($B$1=0,LEFT(AB10,FIND(" ",AB10)-1)+LEFT(AC10,FIND(" ",AC10)-1)+(LEFT(RIGHT(AB10,LEN(AB10)-FIND(" ",AB10)),FIND("/",RIGHT(AB10,LEN(AB10)-FIND(" ",AB10)))-1)*RIGHT(AC10,2)+LEFT(RIGHT(AC10,LEN(AC10)-FIND(" ",AC10)),FIND("/",RIGHT(AC10,LEN(AC10)-FIND(" ",AC10)))-1)*RIGHT(AB10,2))/(RIGHT(AB10,2)*RIGHT(AC10,2)),AE10)

But please don’t mind it until necessary as long story short is as follows:

enter image description here

So, everything was going correctly until the last step where for some reason it rounds off the answer to 2 decimal places. And that is a major problem now as I’m using fractions type value in both the boxes so decimal increase/decrease button is not working.

Now, if it can be helped, I don’t want to change the value setting in the answer box to something other than Fractions (because I wish to answer in fractions only or else I’ll have to calculate further which is neither designed nor intended) but in Verify box its alright to use either of Fractions or Numbers as the value setting.

I have also tried changing the Verify box setting to Number type but the same problem persists and when I click on decimal places increase/decrease for either of the boxes, that still does not work.

Please help. (And thank you reading this question which I know is written too long but I couldn't think of something else to pinpoint my condition/position/problem.)

Edit: My purpose is as follows:

Pls check this image

This image shows the formulas I used in the above image

(Note: Frac2 uses almost same formula as Frac1 and if the formula isn't clearly visible for the Verify cell, due to red bg which is set automatically set by the conditional formatting as the answer wasn't matching, you can check it as it is provided in the main problem already.

The "If-condition" is using a cell which alternates between 0 and 1 to check for true or false condition.)

Here, I am simply trying to create a self-testing math problem that changes every time I solve it and knowing whether my answer is right or wrong.

Frac1 and Frac2 generate random mixed fractions using RANDBETWEEN primarily but since a format is required so CONCAT is used for presentation. Answer box is the box where I type in my answer in the fraction form. Verify box verifies if my answer was right or wrong by changing its colour to red if I'm wrong and to green if I'm right. It is written such that it first converts the given mixed fractions into.... mixed fractions but now calculable and then adds them.

InanimateBeing
  • 121
  • 8
  • 3
    You can't compare floating point numbers for equality, see https://stackoverflow.com/questions/235409/compare-double-in-vba-precision-problem – FunThomas Jan 19 '22 at 18:08
  • This is a pretty long question but you have not provided a clear description of what you're trying to do. What is the user supposed to input, and what are the expected outputs? What is the *purpose* of your process? – Tim Williams Jan 19 '22 at 18:12
  • [Is floating point math broken?](https://stackoverflow.com/q/588004/62576) – Ken White Jan 19 '22 at 18:21
  • @FunThomas: On the one hand, "Don't compare floating point numbers solely for equality" is good advice. (*The Elements of Programming Style*, Kernighan and Plaugher, 1974) OTOH, you certainly *can* compare floating-point numbers for equality. One thing you *can't* do is be certain that two floating-point numbers that *appear* visually identical on a computer will always compare as equal. Another thing you *can't* do is always be certain that two floating point-numbers that are *logically* equal will compare as equal (especially if the logic involves different calculations for each). – Mike Sherrill 'Cat Recall' Jan 19 '22 at 18:38
  • @FunThomas I guess I get that floating point nos. though look apparently same may differ at the very minutest detail which computers see but don't show us (unless into coding). Since I'm using random nos, they might be causing this issue and felt quite convinced after I observed this: My Answer and Verify box had value 28 3/4. Being a terminating decimal, I expected result as equal but no (so the problem isn't rounding off!) excel still shows them unequal! I'm new here and feel FunThomas's comment important, what to do? Also, Thank You everyone! :) – InanimateBeing Jan 19 '22 at 19:24
  • @TimWilliams adding my purpose too for curiosity ones and maybe that might give you idea around my problem and how to solve it. Didn't think so earlier and so did not put it as I thought it would unnecessarily make my question long. Thank you for mentioning this :) – InanimateBeing Jan 19 '22 at 19:27
  • @TimWilliams purpose has been added. – InanimateBeing Jan 19 '22 at 19:51
  • 1
    There's no need to parse fractional entries in Excel using formulas - it does that automatically, so I'm not sure what that long formula is for?. It does matter how you format the cells involved since it affects what you see after entering a fraction (but not the underlying values). – Tim Williams Jan 19 '22 at 20:35
  • @TimWilliams I'm using General type value setting in Frac1 and Frac2 so the long formula first converts the text to numbers and then to fractions to calculate finally. "No need to parse fractional entries" -> I tried this and now I feel crazy, literallly I made a computer but with vacuum tubes and diodes and this suggestion is like that of a modern pc, reduced the code to simple 1 liners though the problem persists. But now I'll update once again. – InanimateBeing Jan 20 '22 at 05:18
  • Update: @TimWilliams I can't update ......without changing the main question significantly, what to do? – InanimateBeing Jan 20 '22 at 05:26
  • Maybe post a new question, and link back to this one? – Tim Williams Jan 20 '22 at 07:07

0 Answers0