Spreadsheet introduction

Ask a question or request a feature related to the website or forum...

Moderator: scott

Post Reply
User avatar
ME
Addict
Addict
Posts: 3512
Joined: Wed Jun 08, 2005 6:37 pm
Location: Netherlands

Spreadsheet introduction

Post by ME »

Because of this post: http://www.besslerwheel.com/forum/viewt ... 978#147978

As manually calculating formula's can be quite tiresome a spreadsheet application is a welcome tool. They have the ability to automate repetitive manual math labor significantly.
Changing a single value could result in the recalculation of a whole sheet full of formula's.

This topic is for the ones who need some help on this.
General info: https://en.wikipedia.org/wiki/Spreadsheet

These are the main choices to look at: LibreOffice-Calc, Microsoft-Excel, Google Docs/sheets.
I personally like Excel and they used to have a Starter-edition for free (I guess the latest version has advertisements). As I'm currently not very fond of the ever growing "always connected"-paradigm, I guess I'd advice the use of LibreOffice.

Spreadsheet comparison: https://en.wikipedia.org/wiki/Spreadsheet
Online spreadsheets: https://en.wikipedia.org/wiki/Online_spreadsheet

- - - -
Attached is a simple example how to implement some formulas as most spreadsheets allow them. It shows two parts:
At the top it is shown how it looks, and where the bordered cells indicate which values may be changed.
At the bottom is shown how formulas are constructed.
(Note: Because of my regional-configuration this example shows a decimal comma instead of a decimal period !)

For spreadsheets in general, perhaps the most important thing to get used to are cell-references. They are a bit hard to read, but luckily make more sense when formulas are constructed and those references are created by a simple point-and-click action.

I hope this info is enough to get anyone started.

eta: just noticed torque should be measure in pound-force-inch in the attached example (I'm not used to this unit): to correct this information, D4 needs to be [Lbf-in]
Attachments
SpreadsheetExample.jpg
Marchello E.
-- May the force lift you up. In case it doesn't, try something else.---
james.lindgard
Aficionado
Aficionado
Posts: 516
Joined: Sat Feb 13, 2016 8:38 pm

Post by james.lindgard »

I like it. It takes a little getting used to. I think your layout is pretty close to what would be needed for an over balanced wheel. I am going to start learning how to use the Excel online, https://office.live.com/start/Excel.aspx
It's something that people might find interesting how well it can organize all the work being done and calculate it. That does add a new dimension.

Jim

edited to add; does the spread sheet that you used have a specific name ?

edited to add, I'll be downloading Apache Open Office. For anyone interested, it is a free download.
http://www.openoffice.org/
User avatar
ME
Addict
Addict
Posts: 3512
Joined: Wed Jun 08, 2005 6:37 pm
Location: Netherlands

re: Spreadsheet introduction

Post by ME »

Marchello E.
-- May the force lift you up. In case it doesn't, try something else.---
james.lindgard
Aficionado
Aficionado
Posts: 516
Joined: Sat Feb 13, 2016 8:38 pm

Post by james.lindgard »

Your welcome. I don't think what I'll be doing will be much more complex than what you did. Since I'll be using a specific design for reference, you'll probably see where I might just need to "tweak" it a little.


Jim

http://www.merriam-webster.com/dictionary/tweak

to change (something) slightly in order to improve it : to make small adjustments to (something)


edited to add; I saved the one Wubbly showed where it graphed your numbers. That's a good visual. Thanks for the links.
User avatar
ME
Addict
Addict
Posts: 3512
Joined: Wed Jun 08, 2005 6:37 pm
Location: Netherlands

re: Spreadsheet introduction

Post by ME »

As told it beats manual (and repeating) labor big time, you are now able to create your own trig-table if and how you want :-)

To create such graph yourself:
Select two columns containing your data by a holding the left mouse-key starting at one corner of such block and release when you reach the opposite corner. Then (at least with Excel) select [Insert]>[Scatter]>select a graph type. This will create a scatter-plot of your data where the first column is used as X/horizontal-data, and the second column as the Y/vertical data.
Selecting more columns will result in multiple plots with the same X-value.
OpenOffice: https://www.youtube.com/watch?v=8_eo8cym4Ro or for general chart ideas: https://youtu.be/YQUSN3avvxQ?t=750

good luck.
Marchello E.
-- May the force lift you up. In case it doesn't, try something else.---
james.lindgard
Aficionado
Aficionado
Posts: 516
Joined: Sat Feb 13, 2016 8:38 pm

re: Spreadsheet introduction

Post by james.lindgard »

That will take me some time to learn. I have copied your example and have been learning how to structure formulas. I think Open Office works a little differently than Excel but am not sure.
Can numbers that are the sum of a calculation be copied and pasted in the same spread sheet or another one ? Sometimes mine will copy and paste but usually it will paste a 0.
I am going to work on a formula for acceleration. This is because levers drop and wheels rotate. Then a transition of weights moving to and from the axle can be factored.
The attached image is what I am wondering about. If I want to copy and paste torque into another cell or into a formula.
Attachments
copy_and_paste_question[1].JPG
User avatar
ME
Addict
Addict
Posts: 3512
Joined: Wed Jun 08, 2005 6:37 pm
Location: Netherlands

re: Spreadsheet introduction

Post by ME »

Perhaps I rushed the information, sorry if that's the case - for me it's second nature.

A program like this is like using any other new tool, try it out on things less important. You could save it with another filename or copy things to another sheet to test things out.
The easiest way to copy things to another sheet is to press [Ctrl A] [Ctrl C] (select all and copy), then select a new sheet by selecting the "sheet2"-tab at the bottom and paste it all [Ctrl V]

The principles should work the same for most spreadsheets programs (the developers were not That creative).
The most unfortunate thing which I didn't realize (compare with Excel) is that Open Office does not by default highlight the cells of your formula as you type: which means that you have to hunt down manually which cells are referenced and used by your formula by using color coding.
Add: Fortunately it seems it can be set, and I strongly advice this setting and it's called "Show references in color" in Open Office/Calc/View, see: Wiki Open Office

Copying formulas
When you look at that first-post-example I mentioned a "Formulas can be copied"-example.
The following options are a bit messy to read and probably only make some sense after playing with it; I try to write it down anyway.

Option a.
In that example I copied from cell G3 having a formula "=E3/12".
When I would paste it one cell below to G4 (column G, Row 4) then the formula would be changed relatively; the G4 formula would become "=E4/12".
But because there's no information in cell E4 (empty, thus handled like 0) the output would be zero: =0/12
As you can see the formula is relative dependent on the information which is two cells at its left.

Option b.
When you are in "edit"-mode (by double-click or F2) then you could copy the whole formula in G3 as text: Use the mouse, or if you're keyboard savvy then [Home] [Ctrl Shift End] to select all of the formula and copy by [Ctrl C]
When you paste [Ctrl V] in G5 then the formula would be as if you retyped it. The resulting value would be the same as in G3.
But if you now copy G5 to G6 as done like option-a, the relative cell (E3) would still be two up and two left: giving a wrong result.
The benefit of this option is that you don't have to retype the formula but can make a small alterations while keeping the original: especially handy when formulas grow bigger.

The torque formula.
If you now look at cell C17 you see a formula with three references (C14, C11 and C3) which is actually resp. (3 up, 6 up, 14 up)
When you copy things according to option-a then things start to point to the wrong fields. So a copy to the cell below will result in a formula calculating the product of (1 Nm conversion, an empty cell, and a vertical position): in this case a nonsense formula.
When you replace "C3" in the C17-formula by "$C$3" then you can observe that copying this formula would keep this "$C$3": it locks the reference when you copy, and thus keeps pointing at the 9.8 value (as it should be). And the same should been done with "$C$11".

Copy failure
It depends on your objective how your question should be answered:

1. If you want to make room for something else then you could right-click on the row-number for example and select "Insert row", or highlight multiple rows with your left-mouse-key then right-click on those and "Insert row" should insert multiple rows.
Tip: try to right-click almost anywhere to discover functionality at where you click.

2. If you want to do multiple torque calculations for different angles, then you might want to create a table as somewhat demonstrated in this video
When you drag a formula like it's shown there, you can try and see what happens when you include or omit such added "$". Especially with highlighting enabled.
Marchello E.
-- May the force lift you up. In case it doesn't, try something else.---
james.lindgard
Aficionado
Aficionado
Posts: 516
Joined: Sat Feb 13, 2016 8:38 pm

re: Spreadsheet introduction

Post by james.lindgard »

ME,
Don't apologize. When I saw how organized your example was, I thought you might have a lot of experience with this and it does show. And I do appreciate your taking the time to help me. Who knows, maybe some others are checking out what a spreadsheet can do.
I am saving your posts so I'll have them for future reference. And I have modified your example spreadsheet so it calculates net torque in both N-m and in. lbs. I like working both in SAE and metric so it's convenient for me.


Jim

edited to add; the dimensions I am calculating are 17 oz.'s @ 48 in. with an over balance of 6 in. or as shown in the spreadsheet, 1/2 Kg @ 60/75 cm's.

p.s., one reason for the 2 spreadsheets is that will make it easy (I think) to try the graph you suggested.
Attachments
Net_Torque[1].JPG
User avatar
WaltzCee
Addict
Addict
Posts: 3361
Joined: Sun Dec 09, 2007 9:52 pm
Location: Huntsville, TX
Contact:

re: Spreadsheet introduction

Post by WaltzCee »

I really enjoy considering translations back and forth into every language on earth.

This might be something that some don't want to bother with yet you have no idea how much it blows my skirt up.

Oooo laaa laaaaa!
........................¯\_(ツ)_/¯
¯\_(ツ)_/¯ the future is here ¯\_(ツ)_/¯
Advocate of God Almighty, maker of heaven and earth and redeemer of my soul.
Walter Clarkson
© 2023 Walter W. Clarkson, LLC
All rights reserved. Do not even quote me w/o my expressed written consent.
Post Reply