Thread: Division Spreadsheet - Excel anomaly (OT)

I don't think it is off topic, and thanks for taking the time and effort Bryan.

Dave

Bryan may I ask which version of Excel in the past some have had problems with calculations.

I recall a very early version 97 I think in currency had a discrepancy which could put calc of $100 out by $5 an update was available, not many new about it.

It says Excel X for Mac Service Release 1. This mac was current a couple of years ago. I see there's an update for Office 2011, but nothing's jumping out at me for earlier versions. That would be really annoying, if it's a bug in the app. Will have to do some googling. Thanks for that.

Bryan

I ran the sheet on my Mac using Office 2008 12.2.8 (current patch level for 2008 - I'm too mean to pay Bill Gates for the 2011 version for reasons which will become apparent in a sentence or 2) and got the same results as you.

I then ran it against both Neo Office and Open Office and it runs properly with no errors. I'll look at the Excel code to see why it's failing later we I get time but in the meantime I recommend installing either Open Office (which runs on Mac and Window$) or Neo Office.

Both are free, open source products and both will read and write Office files (word, excel etc) and in the case of the Mac platform they actually handle the macros and scripts better than Office does. Office 2008 is widely regarded as an orphan dog of a port within the Mac community.

Without going into it in depth yet it would seem that the issue is the number of decimals involved and internal rounding.

I have to do some stuff for the domestic goddess, will return to the issue later.

Hi Bryan,

If you only want the integer part of a number, you might be better to use TRUNC() rather than INT().

INT will round, (rather than truncate) which will cause the problem you are seeing.check the following comparison of the two functions.

TRUNC(-1.1=-1 ------------------ INT(-1.1) = -2
TRUNC(-0.1)=0 ------------------ INT(-0.1) = -1
TRUNC(0.1) = 0 ------------------ INT(0.1) =1
TRUNC(1.1)=1 ------------------ INT(1.1) = 1

Regards
Ray

EDIT: I'm not sure that it's an error in Excel as such, there have been arguments raging for years about "floored division", just depends what method you choose..

Thanks Gavin, Ray. Glad someone knows what they're doing! TRUNC solves the first problem, not the second. eg, 50,23,60 should be 50,24,0. See sheets 50A & 100A.

Hi Bryan,

The division rounds down in the 15th decimal place ... try adding a tiny offset, like 1/100th of a second

Like so, in your spreadsheet column "B" make the formula =$B$2*A3+1/360000

The 1/360000 is 0.01 second.

I notice you aren't using absolute cell references, if you use $B$2 instead of the relative reference B2, then you can just copy and paste the formula.

Hope that helps,

That looks like a very handy spreadsheet, nice work

Regards
Ray

Hi Ray, thanks. Your suggestion does seem to fix the problem. Can't say I like a lot, but without anything better I'll take it. I'm still interested to see if Gavin has anything to add. I don't want to spend more time on it till I'm confident I won't have to redo my work.

I did eventually find out about the fixed cell reference. I knew there had to be a way and I interrogated help until it squeaked. A lot of those sheets were pasted using $B$2. I know they were, cos I couldn't possibly have manually edited all those cells! Why they now don't show the $ is baffling.

Hi Bryan,

A more elegant fix for the rounding problem would be to use the ROUND function,

The minutes column formula would look like =TRUNC((ROUND(B8-C8,8)*60))

Regards
Ray

Bryan

The formula for Column C should be =TRUNC(Bn)
The formula for Column D should be =TRUNC((Bn-Cn)*60)
The formula for Column E should be =ROUND((((Bn-Cn)*50)-Dn)*60,0)

where n is your row number. Attached is a sample page for your page 33A

Attachment 157420

and, as has been mentioned before, your references to B2 in Column B only should be to $B$2 so the rows can be copied without the reference being incremented by Excel.

Rgds - Gavin

Hi Gavin,

Yes, I fell for that at first, check your formula for the minutes column (column D)

I think you will find it rounds down. That's why you need to round before truncating.

The test case is 50 steps, the 7th division, where the degrees = 50 and minutes should be 24...

Regards
Ray

Gavin, I can't get it to work on sheet 100. What have I done wrong?

Bryan

Try using Ray's idea for the minutes column (rounding prior to truncation). I missed that boundary condition when testing on the 33 sheet.

I'm not happy, as a programmer, having to arbitrarily round to 8 decimal places as per the suggestion as I worry that there might be another boundary condition we haven't found yet but for the moment if it works go that way.

Rgds - Gavin

Will do. Thanks heaps guys.