Thread: Division Spreadsheet - Excel anomaly (OT)

Agreed, floating point can be a pain in the a$$. When I started programming in 1972 we used integer arithmetic whenever possible because floating point was so slow on the mainframes (this is before PCs were developed). In the case of currency calculations we just multiplied everything by 100 to make the cents part of the integer value and then "fudged" the decimal point in the reporting. Simple days.

Originally Posted by Gavin Newman

Agreed, floating point can be a pain in the a$$. When I started programming in 1972 we used integer arithmetic whenever possible because floating point was so slow on the mainframes (this is before PCs were developed). In the case of currency calculations we just multiplied everything by 100 to make the cents part of the integer value and then "fudged" the decimal point in the reporting. Simple days.

You were programming mainframes at age 17? wow... I programmed in Fortran in 1967 on a PDP-11 with punched cards.. not a pleasant experience.. my first *real* computer was a Burroughs L2000, mid 1970's with 512 word hard drive (each word was 24 bits, if I recall correctly) all programmed in assembler. Programs loaded with a paper tape reader. Had a really good golf-ball typewriter.

Much later (late 70's early 80's) I got into programming 6502's in assembler. Designed and built many real time control systems mostly in Forth for many years, Nowadays it's all C, linux, pic micro's and so on.

I did once try to implement Donald Knuth's Mix floating point, but never finished it, fixed point with rational fractions was faster and more accurate.

I get unreasonably angry when I see the vast resources of PC's these days and they still can't get it right... ( witness 50.4-50=0.3999...Thanks Bill Gates!)

Still, brings back fond memories of simpler times, as you say...

Regards
Ray

I always wanted to be a mechanic but was convinced by my parents to work in a Bank. The Bank decided to computerise and I was "volunteered" to learn how to use the thing so I was programming assembler & COBOL on an NCR 8565 in late 1972 after 4 weeks training from the supplier. I hadn't heard about computers prior to leaving school as I was from the bush. I too remember punch cards and CRAM decks. We used to boot the NCRs with card decks of 200+ cards, dropping the deck was an exercise not to be repeated!

Since then it's been assembler, C, COBOL, UPG, PL/1 and any number of other, now dead, languages on NCR, VAX, IBM, Amdahl, Wang and other systems as well as network systems programming.

Nowadays I'm working with telephony, networking, Linux systems programming and all sorts of other interesting stuff for the same bank and designed and wrote their NMS for them single handed so I don't suppose I'll ever be a full time motor mechanic.

What makes me laugh is seeing the world turn full circle, in early days all the power was in the central mainframe and users had dumb 3270 style terminals. Then the PC came along and the proponents decreed that the mainframe was dead as all power would sit on the users' desks as the PC was the future. After a while they found out how hard it was to manage a distributed system like that and now we have SOE based PCs where all files are stored centrally, most applications are web based with the main processing happening within the computer room - in essence we are back to the old mainframe arrangement and the PC on the desk is largely a dumb, web client. And after years of deriding IBM and Amdahl for water cooling their CPU chips we now see PCs doing exactly the same thing 20+ years later. There's nothing new under the sun it would seem.

I think we've both lived through the best times in IT, we had much more freedom and a much wider range of experiences than they have today where it's (for good reasons I suppose) much more tightly controlled and specialised.

Chasing this up further Ray, your sample calculation gives the same erroneous results in Open Office & Neo Office but both these applications give good results for Bryans sheet 33A (but not 50A & 100A) despite both adhering to IEEE 754 (Neo Office is a fork of Open Office so it's no surprise they behave similarly).

There's a MS KB article that refers to the issue and relates it to adherence with IEEE 754 and storage of fp numbers in binary format.

Floating-point arithmetic may give inaccurate results in Excel

Rounding And Precision In Excel

The "precision as shown" option assists in resolving the issues with some of Bryan's sheets in Excel on the Mac (33A for example) but not the 50A or 100A sheets, this might be resolved by changing the formatting of the cells but I don't have time to play with it at the moment, I'm off to play with my race kart for a bit of mechanical therapy.

I think that the ROUND and TRUNC combination is as good as we are going to get here. Ugly but it works.

I've finished populating the data. I've used the revised formulas for the problem sheets, plus the 50 or so new sheets. I didn't bother changing the existing sheets that are behaving themselves because that's a lot of work.

I'm now drafting a little readme. Do I need to say anything about what app is used or is this now pretty bulletproof? I'm trying to keep it simple. This is what I've got so far:

This spreadsheet is intended to enable divisions with only a rotary table and no division plates. If you want to divide a circle into, say, 16 equal parts, select the sheet labeled 16 and print the page. The last 3 columns are the degrees, minutes and seconds to set on your rotary table for each consecutive step. You may want to tick each one off as you go.

Tables are only included for divisions up to 127, that being the highest number commonly used for lathe change gears. If you need higher numbers it's easy to do. This file is write-protected, so first do a save-as to create a version you can edit.

At the bottom left of the screen, click this >| to go to the far right of the tabs. Right click the 360 tab and insert a new worksheet. If for example you're doing 153 divisions, name the sheet 153.

Go to the 360 tab, select all and copy. Paste to your new sheet. Now there are two small edits needed. First, cell B2 will say =360/360. Change the last number to your divisor, in this case 153. Now just delete all the rows below that number. In our example, delete rows 155-361 inclusive. (There's an offset because of the title row.)

Check that the last row says 360 degrees, 0 minutes, 0 seconds. If it doesn't, something went wrong. Make sure B2 is correct.

Errors, suggestions, comments etc may be addressed to: shed at ptmedia dot com dot au.

This file may be freely distributed.

I have to say I'm a little bit nervous about a 'release' until it's had a bit of testing. I mean actually putting it to use to make sure it does what it claims. I can't do this myself as yet. Anyone want to volunteer?

Originally Posted by Bryan

I have to say I'm a little bit nervous about a 'release' until it's had a bit of testing. I mean actually putting it to use to make sure it does what it claims. I can't do this myself as yet. Anyone want to volunteer?

Bryan

Just release it, Bill Gates made billions of dollars releasing half-tested code and letting the public do his beta testing for him so it's not as though you'd be starting a trend......

Hi Bryan,

I think it's pretty close, but as always, the final testing, in actual usage will probably throw up a few suggestions for improvements. I would definately put it "out there" for "evaluation".

Congratulations on undertaking a major task, that I'm sure everyone who uses it will appreciate the effort required.


Hi Gavin,
I confess to being more interested in building and programming computers to control machines to do things, I did once spend a bit of time in the finance dept doing financial modelling (an old ICL ME29 mainframe) but nothing beats real-time machine control.

My favourite has to be hydraulic control systems, it's nice to be able to whack holes in 1/2" steel plate with a few strokes of the keyboard!.. These days I still do consulting work on VFD based pump control systems, mostly large multi-pump systems, and being semi-retired I also do a lot of weird electronic control stuff for my son's artwork projects. His latest being a robotic origami project that we did together at Ars Electronica in Austria last year. a bit more info here.. Oribotics by Matthew Gardiner | Home

Nice to know there are still a few of us old-timers around who can remember the world of 8" floppy disks and the days before pc's...

Regards
Ray

Ray, that's so cool! Very impressive.

Ok well here it is. Have a play. Let me know.
Never done write-protection before so I don't know if I've done it properly.

Edit: Attachment deleted. See below for current version.

The read only attribute works when opening the file with Excel but not if I open the file using Open Office or Neo Office where the sheets are writeable.

The other approach is to use Data Protection, that is obeyed by all 3 applications. You need to select each sheet in turn and ensure all cells are locked (select the entire sheet then format the cell protection) and then protect each sheet with a password (Tools | Protection | Sheet) - supply a password and repeat for each sheet in turn. Finally lock the workbook with Tools | Protection | Workbook.

Unfortunately Excel doesn't have a way of protecting all the sheets in one action, you can use a VBA script on it under Windows, haven't tried it on Mac but I don't think it supports VBA until Excel 2011.

Rgds - Gavin

this thread is too hard for me...lol
and I thought I knew a little about Excel...it seems I know bugga all....
some of you guys are amazing...your comp skills and machining skills...i might just stick to fishing..


i cant do that either...so she says

Originally Posted by eskimo

this thread is too hard for me...lol
and I thought I knew a little about Excel...it seems I know bugga all....
some of you guys are amazing...your comp skills and machining skills...i

You and me both!

Bryan

I've sent a modified version of your sheet and will PM you with details,

Rgds - Gavin

Updated version with data protection and display issues fixed - thanks to Gavin.