Excel GCD function giving strange results

0
votes

I'm working on a formula to reduce fractions in Excel. The numerator and denominator are in separate cells. The numerator is in cell I38 and has a value of 1015. The denominator is in cell I40 and has a value of 100000.

I'm trying to use the GCD function to determine when I can reduce the fraction.

However, the GCD function is returning crazy results: enter image description here

Am I misunderstanding the GCD function and how it works or is there a bug in Excel?

MS Excel 2016 on Windows 10, if that helps.

EDIT: When cell I38 has a number in it, the formula works fine. When it has a formula in it, the wrong answer is returned. Here is a screenshot of the formula: enter image description here Here is the formula:

=G38*10^IF(C39=INT(C39),0,LEN(MID(C39-INT(C39),FIND(".",C39,1),LEN(C39)-FIND(".",C39,1))))
1
excelexcel-formulagreatest-common-divisor
@JvdV I think you're misreading the image. The first value is in the cell I38 (letter I) and the value is 1015. The second value is in cell I40 and the value is 100000. The dialog box you're looking at is the results of Excel's built-in GCD function. - BoltBait
If you're getting the correct result... perhaps it is a problem with my version of Excel. Or, maybe it is because the cells I'm using are calculated and not just numbers. - BoltBait
Yes, I'm getting the right results. See this. I'm in Excel 365 though so I can't verify for Excel 2016. Calculated fields didn't change that. Can you show you calculations? - JvdV
@JvdV it's fixed! I will edit solution above. - BoltBait
@BoltBait The fact that you took this long to figure it out tells you it's not that simple. I'll bet this has bitten many people and they would appreciate a clear answer on SO. You'll be saving thousands of hours. - chthonicdaemon

1 Answers

1
votes

The problem was I forgot to cast the result of that formula to INT (even though the result of the formula was always going to be an INT, Excel didn't see it that way). Once I added that, everything worked fine:

=INT(G38*10^IF(C39=INT(C39),0,LEN(MID(C39-INT(C39),FIND(".",C39,1),LEN(C39)-FIND(".",C39,1)))))

Thanks goes out to user @JvdV for helping me track that down in the comments.