Anybody a whiz with Excel spreadsheets?

Anybody a whiz with Excel spreadsheets?

Joined: January 1st, 1970, 12:00 am

December 30th, 2017, 5:07 am #1

Okay, here's the situation: In order to finish adjusting the travel of my CNC lathe, I need to slightly alter what's known as the "turns ratio" of my stepper drives. It basically compensates for minor inaccuracies, so that when the computer commands the carriage to move 1.0000", it moves purty durn near to 1.0000" as possible, given the limitations of my particular setup.

It's an easy adjustment in the software, but you first have to do a little math to figure out how to adjust it. In this case, one puts a dial indicator, or other precision measuring device, on the axis to be adjusted, and to tell the software to move that axis a fixed distance, like one inch.

And, if you come up with, say. 0.991" of actual movement, or 1.014" of actual movement, you adjust the "turns ratio" which compensates for that error.

One of the guys over on the Centroid board came up with an Excel spreadsheet, that does the calculation for you- just enter how much you told it to move, and how much it actually moved, and it gives you a compensation number to enter into the "Wizard" in the software.

The problem is, I can't open it. Others have had trouble with it too, so it's not just me. I don't have Excel on any of my computers, and would rather not have to buy a copy from Microsoft just do do this one quick task.

I tried a 'free' reader download, which could open it, but can't alter the document- it's read-only. So the calculation won't work.

OpenOffice was recommended to me, so I downloaded that, and IT says the fiel's corrupt, and can't read or open it.

If anyone would like to give it a whirl here's the link to the XLS file. What I'm wondering is if one of you could extract the actual formula out of the spreadsheet, and post it in more or less plain English, so I can just use a friggin' calculator on the damn thing.

Thanks.
Doc.
Reply
Like
Share

Joined: September 12th, 2014, 3:32 am

December 30th, 2017, 5:29 am #2

Ah, it's a newer 'xml' based Excel Spreadsheet (i.e., the extension is supposed to be .xlsx instead of .xls) It looks really simple, actually.

There's three input cells: commanded distance moved ('A'), actual distance moved ('B') and currently programmed ratio ('C') in rev/inch.

There's a cell that does A/B ('D'), the cell below it does B/D, and the result is shown as D*C.

hope that helps!
Reply
Like
Share

Joined: January 1st, 1970, 12:00 am

December 30th, 2017, 5:55 am #3

First, would it help if I just renamed the file to .xlsx?

And second, that helps a bit on the math, but I can't open the document at all. I have no idea which number to enter where.

Third, since math is definitely not one of my stronger suits, can you dumb the math down for me a bit? I'm still back with the grade school kids, where 1+2=3. (Usually. )

Doc.
Reply
Like
Share

Joined: May 22nd, 2016, 10:05 pm

December 30th, 2017, 6:01 am #4

Okay, here's the situation: In order to finish adjusting the travel of my CNC lathe, I need to slightly alter what's known as the "turns ratio" of my stepper drives. It basically compensates for minor inaccuracies, so that when the computer commands the carriage to move 1.0000", it moves purty durn near to 1.0000" as possible, given the limitations of my particular setup.

It's an easy adjustment in the software, but you first have to do a little math to figure out how to adjust it. In this case, one puts a dial indicator, or other precision measuring device, on the axis to be adjusted, and to tell the software to move that axis a fixed distance, like one inch.

And, if you come up with, say. 0.991" of actual movement, or 1.014" of actual movement, you adjust the "turns ratio" which compensates for that error.

One of the guys over on the Centroid board came up with an Excel spreadsheet, that does the calculation for you- just enter how much you told it to move, and how much it actually moved, and it gives you a compensation number to enter into the "Wizard" in the software.

The problem is, I can't open it. Others have had trouble with it too, so it's not just me. I don't have Excel on any of my computers, and would rather not have to buy a copy from Microsoft just do do this one quick task.

I tried a 'free' reader download, which could open it, but can't alter the document- it's read-only. So the calculation won't work.

OpenOffice was recommended to me, so I downloaded that, and IT says the fiel's corrupt, and can't read or open it.

If anyone would like to give it a whirl here's the link to the XLS file. What I'm wondering is if one of you could extract the actual formula out of the spreadsheet, and post it in more or less plain English, so I can just use a friggin' calculator on the damn thing.

Thanks.
Doc.
I uploaded the file to Google Docs and opened it as a Google Sheet. See if this link works.

https://docs.google.com/spreadsheets/d/ ... sp=sharing
Love thou the rose, yet leave it on its stem. -- Edward Bulwer-Lytton
Reply
Like
Share

Joined: May 22nd, 2016, 10:05 pm

December 30th, 2017, 6:05 am #5

Google's office suite works pretty well.
Love thou the rose, yet leave it on its stem. -- Edward Bulwer-Lytton
Reply
Like
Share

Joined: November 8th, 2017, 3:33 am

December 30th, 2017, 6:19 am #6

First, would it help if I just renamed the file to .xlsx?

And second, that helps a bit on the math, but I can't open the document at all. I have no idea which number to enter where.

Third, since math is definitely not one of my stronger suits, can you dumb the math down for me a bit? I'm still back with the grade school kids, where 1+2=3. (Usually. )

Doc.
My letters for the values won’t match the spreadsheet. For example you command it to move 1 inch call that C for command. It uses the current factor, CF, to multiply C and turns some number of turns that gets the actual distance A. So we need a new factor, NF, to replace the current one so we get what we asked for in the first place. Make a ratio between the new factor and command equal the ratio of current factor and actual we get NF=CF/A * C.

I didn’t open the spreadsheet, who knows where it has been and what it may have contracted. But, I was able to take a peek at the numbers in it and if plug them into that formula I get the number in the spreadsheet.

Good luck



Reply
Like
Share

Joined: July 31st, 2016, 12:48 pm

December 30th, 2017, 8:20 am #7

First, would it help if I just renamed the file to .xlsx?

And second, that helps a bit on the math, but I can't open the document at all. I have no idea which number to enter where.

Third, since math is definitely not one of my stronger suits, can you dumb the math down for me a bit? I'm still back with the grade school kids, where 1+2=3. (Usually. )

Doc.
...no, renaming won't help; but you can do the calculation with paper and pencil.

-> I've sent an image you by email.

You can do the calculation either in imperial or metric. Start at (A) or (G), and work down from there to the answer.

When a step says for example "L=I*K", it means "take the values from I and K, multiply, and name the answer L"... strictly speaking it's more of an assignment ("<-"), not an equation. If you have any questions, don't hesitate to ask me
Reply
Like
Share

Joined: January 1st, 1970, 12:00 am

December 30th, 2017, 10:42 am #8

Okay, here's the situation: In order to finish adjusting the travel of my CNC lathe, I need to slightly alter what's known as the "turns ratio" of my stepper drives. It basically compensates for minor inaccuracies, so that when the computer commands the carriage to move 1.0000", it moves purty durn near to 1.0000" as possible, given the limitations of my particular setup.

It's an easy adjustment in the software, but you first have to do a little math to figure out how to adjust it. In this case, one puts a dial indicator, or other precision measuring device, on the axis to be adjusted, and to tell the software to move that axis a fixed distance, like one inch.

And, if you come up with, say. 0.991" of actual movement, or 1.014" of actual movement, you adjust the "turns ratio" which compensates for that error.

One of the guys over on the Centroid board came up with an Excel spreadsheet, that does the calculation for you- just enter how much you told it to move, and how much it actually moved, and it gives you a compensation number to enter into the "Wizard" in the software.

The problem is, I can't open it. Others have had trouble with it too, so it's not just me. I don't have Excel on any of my computers, and would rather not have to buy a copy from Microsoft just do do this one quick task.

I tried a 'free' reader download, which could open it, but can't alter the document- it's read-only. So the calculation won't work.

OpenOffice was recommended to me, so I downloaded that, and IT says the fiel's corrupt, and can't read or open it.

If anyone would like to give it a whirl here's the link to the XLS file. What I'm wondering is if one of you could extract the actual formula out of the spreadsheet, and post it in more or less plain English, so I can just use a friggin' calculator on the damn thing.

Thanks.
Doc.
Your help is much appreciated!

In the end, it seems the math was quite simple: DRO reading (that is, the distance the computer thought it moved) divided by the distance it actually moved, multiplied by the "turns ratio"- that is, the number of turns the stepper motor shaft has to make in order for the axis to move one inch.

The spreadsheet makes it fairly simple, in that you can easily keep "fine tuning"- that is, test, change the ratio, test again, adjust the ratio again, etc. until you get it dead-nuts on.

The problem is it seems to "swing" a lot- you overadjust, then re-underadjust, then re-overadjust, etc. as you close in on the number. I admit I cheated a bit, and got close with the above method, then just made manual changes in regular amounts.

That is, the calculation might have given me 15.103462, but that still gives me an error of, say, 1.0065" instead of one inch. Rather than go through the calculation which would overshoot and give me a .997", I just changed the number by... well, blind stab.

I moved it to, say, 15.1035, which wasn't enough, then 15.104, which was a lot closer, then 15.1045, and so on. (All numbers just examples for illustration.)

Basically the same procedure we used in setting up Mach 3.

Doc.
Reply
Like
Share

Joined: February 2nd, 2015, 4:36 pm

December 30th, 2017, 8:39 pm #9

Google's office suite works pretty well.
Agree--Google Sheets work well, very similar to Excel. We use them to track average production time, which is not that different from a ratio formula.
Reply
Like
Share