### Author Topic: Excel Formula Question  (Read 1054 times)

0 Members and 1 Guest are viewing this topic.

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Excel Formula Question
« on: November 20, 2023, 03:19:57 am »
I have a spreadsheet I created and simplified it for purposes of this question.

Attached is a screen shot and I labeled row 1 to reflect the formulas in row 2 rather than attach the spreadsheet since Excel saves my personal information in the about section.

When I change the number in E7, column E2 should change, but it doesn't. I've managed to figure out that it's not happy including column B in the formula, so I think it's getting stuck with some sort of circle calculation, however, it's not giving any errors.

Can someone tell me what I'm doing wrong and how to get around this?

#### T3sl4co1l

• Super Contributor
• Posts: 21681
• Country:
• Expert, Analog Electronics, PCB Layout, EMC
##### Re: Excel Formula Question
« Reply #1 on: November 20, 2023, 03:38:51 am »
To show contents, prefix with an apostrophe, or change the view mode to display equations.

The cells contain an assignment expression (prefix equals sign), right?

You can also use the Formulas / Trace Precedents/Dependents to follow relations around visually.

There's also an option to evaluate the whole sheet stepwise (which permits, but may still warn of, dependency loops), which, I forget where that's done.

Tim
Seven Transistor Labs, LLC
Electronic design, from concept to prototype.
Bringing a project to life?  Send me a message!

#### Veteran68

• Frequent Contributor
• Posts: 727
• Country:
##### Re: Excel Formula Question
« Reply #2 on: November 20, 2023, 03:41:29 am »
Worked fine for me. I changed E7 from 0 to 5 and B2 updated to 10 as expected.

I think you've got an issue with the formula in B2. I'd delete it and re-enter it as "=5+E7"

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Re: Excel Formula Question
« Reply #3 on: November 20, 2023, 03:48:29 am »
I didn't remember an option existed to show formulas.

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Re: Excel Formula Question
« Reply #4 on: November 20, 2023, 03:49:55 am »
Quote
Worked fine for me. I changed E7 from 0 to 5 and B2 updated to 10 as expected.

E2 should change and it's not; B2 works fine.

#### ejeffrey

• Super Contributor
• Posts: 3718
• Country:
##### Re: Excel Formula Question
« Reply #5 on: November 20, 2023, 03:53:46 am »
Check your math, E2 doesn't depend on E7.  The B2 terms cancel.

The following users thanked this post: newbrain

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Re: Excel Formula Question
« Reply #6 on: November 20, 2023, 04:01:25 am »
I put 'change this number' because this is a number that manually gets changed.

Currently it's 0, but this gets changed manually.

Basically this spreadsheet does some calculations, and then I can manually add a 'fudge' number which is what E7 does.

#### ejeffrey

• Super Contributor
• Posts: 3718
• Country:
##### Re: Excel Formula Question
« Reply #7 on: November 20, 2023, 04:15:28 am »
Well, that's just not what the formulas you wrote do.  E2 doesn't depend on E7.   Just write the formula out and expand it by hand.

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Re: Excel Formula Question
« Reply #8 on: November 20, 2023, 04:19:23 am »
I'm confused because B2 changes as a result of (manually) changing E7, and then E2 takes B2 divides by D2, and multiples it by C2.

#### ejeffrey

• Super Contributor
• Posts: 3718
• Country:
##### Re: Excel Formula Question
« Reply #9 on: November 20, 2023, 04:36:55 am »
It's because C2 *also* depends on B2.

E2 = C2 * B2 / D2
-> (substitute C2 = E5 * A2 / B2)
E2 = E5 * (A2 / B2) * B2 / D2

The B2 terms cancel.

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Re: Excel Formula Question
« Reply #10 on: November 20, 2023, 04:53:46 am »
That makes more sense the way you wrote it, just seems to not make sense when I stare at Excel.

I need to figure out a better formula, but thought the formulas I had fitted the purpose of my spreadsheet. Apparently I was wrong and managed to cancel terms.

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Re: Excel Formula Question
« Reply #11 on: November 20, 2023, 05:12:34 am »
Just to give some insight on this spreadsheet. Long story short, it's being used to calculate the speed of a drill press that has three pulleys.

The motor pulley is one size, the idler pulley the motor connects to is another size, and the drill pulley is another size that the idler connects to.

So I take the ratio of the motor and idler pulley and multiply that by the motor speed to get the speed of the idler pulley. Then I take the ratio of the drill pulley and idler, and multiply that by the idler speed to calculate the speed of the drill.

Unless I'm thinking of this wrong, I need to use the calculated speed of the idler to calculate the speed of the drill pulley. Obviously from what you showed I'm forcing a cancellation, but not sure how else to perform the math.

The 'fudge' number is needed for a certain purpose and why E7 is there.

#### newbrain

• Super Contributor
• Posts: 1719
• Country:
##### Re: Excel Formula Question
« Reply #12 on: November 20, 2023, 08:47:29 pm »
So it's just a cascade of three gears/pulley?

Well, your formulas are correct, and - as you noticed - the speed of the final gear does not depend on the intermediate one, only on the ratio between the first and the last.
This is also true for any number of intermediate gears.

You should have played more with Lego or Meccano gears as a kid!
Nandemo wa shiranai wa yo, shitteru koto dake.

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Re: Excel Formula Question
« Reply #13 on: November 21, 2023, 02:27:11 am »
Quote
Well, your formulas are correct, and - as you noticed - the speed of the final gear does not depend on the intermediate one, only on the ratio between the first and the last.
This is also true for any number of intermediate gears.

Is this what I've been doing wrong this entire time? The way I've been doing it:

I take the circumference of the motor pulley and divide it by the circumference of the idler pulley, and multiply it by the motor speed (1800RPMs). That gives me the speed of the idler. Then I take the circumference of the drill pulley, divide that by the idler pulley circumference, and multiply that by the idler speed.

This is the wrong way and why I'm getting cancelations?

How is it that I don't need to include the idler speed since that changes the drill speed since it's an intermediate driver?

#### ejeffrey

• Super Contributor
• Posts: 3718
• Country:
##### Re: Excel Formula Question
« Reply #14 on: November 21, 2023, 04:09:33 am »
If the idler pulley is a single diameter -- not itself stepped with different diameters for the motor and drill belts -- then it's size doesn't affect the output speed.  One way to look at it is that the linear speed of both belts will always be the same.

#### Ian.M

• Super Contributor
• Posts: 12860
##### Re: Excel Formula Question
« Reply #15 on: November 21, 2023, 04:36:43 am »
I assume this is related to your previous drill press topic: https://www.eevblog.com/forum/mechanical-engineering/calculating-total-number-of-speeds-on-drill-press/

For each belt, divide the pitch diameter of the driving pulley by the pitch diameter of the driven pulley to get the factor it transforms the speed by.   Multiply the factors for both belts together to get the overall factor, then multiply the motor speed by it to get the spindle speed.

N.B. it is almost certain you have measured the pulley diameters wrong.  A V belt drives on its flanks.  Unless worn out, the inside flat surface across the tip of the V doesn't even touch the pulley.   The pitch (effective) diameter is approximately where the belt reinforcement sits in the V groove.  Measure how far in from the back of the belt it is (preferably on a new belt), then subtract twice that from the diameter of the pulley at the back of the belt when its held tightly round the pulley.   You can measure that straight off the larger pulley of any pair with the belt in place (assuming you've got large enough callipers), but for the smaller  pulley you'll need to slacken it enough to hold it in to get >180° wrap.

Finding the pitch diameter with a link belt will be significantly more difficult.  I'd probably measure it with a compatible profile V belt wrapped round each pully, then adjust the fudge factor (was double the thickness between the back of the belt and the reinforcement) you subtract from the measured diameters (over the belt) which should be the same for all,  till the measured spindle speed comes out right.
« Last Edit: November 21, 2023, 08:36:44 am by Ian.M »

#### IanB

• Super Contributor
• Posts: 11885
• Country:
##### Re: Excel Formula Question
« Reply #16 on: November 21, 2023, 08:26:08 am »
Measuring the gear ratio of a belt drive can be done more easily using the belt itself to help with the measurement. Make a mark on the belt lined up against a point on the first pulley. Now turn the pulley through one full revolution, and make a second mark on the belt. The distance between the two marks is the circumference of the pulley. Repeat the same procedure with the second pulley. Now the gear ratio is the ratio between the two measurements.
« Last Edit: November 21, 2023, 08:27:57 am by IanB »

The following users thanked this post: Ian.M

#### Ian.M

• Super Contributor
• Posts: 12860
##### Re: Excel Formula Question
« Reply #17 on: November 21, 2023, 08:43:21 am »
Noted for future use!  You've just removed the need for big callipers, and improved the accuracy as well as you will be measuring the circumference as the belt sits under tension.

Also, there's no need to convert to diameters, (as long as you use *all* circumferences) as the Pi drops out when you calculate the ratios.

#### bostonman

• Super Contributor
• Posts: 1789
• Country:
##### Re: Excel Formula Question
« Reply #18 on: November 21, 2023, 03:05:55 pm »
Quote
I assume this is related to your previous drill press topic:

Yes, you're correct. This wasn't meant to deviate towards repeating the drill press thread. This is what I've done that led me up to this thread. I measured speeds on every belt combination (on both the idler and chuck pulley along with the motor) using a meter and the majority are extremely different than the tag on the press (the high speed should be 2800 and I"m getting around 3900 - I don't have the numbers with me at the moment). Now, I went ahead and measured the circumference of each pulley by tightly wrapping a string around the base of each one and then measuring the length of string on a tape measure. This is the circumference, and, from that, I could also (obviously) get the diameter if I cared to use this or have a need to use it.

Trying to find the "pitch diameter" and/or measuring error factor, I entered all the measurements into Excel and created a "fudge" number cell that will allow me to enter a number that adds a larger (or smaller) circumference and all the cells will update accordingly. This will allow me to see if I can get all the calculated numbers equal to the numbers I measured.

As an example: if one pulley is 3" circumference, and I have all the cells calculating the ratios from the motor, speeds, pulley ratios, etc... Then in my "fudge" number cell I may enter 0.3" and see if that makes the drill press speed what I measured.

What led me to post on this thread is that my Excel formula wasn't working and I thought it was an Excel formatting issue caused by me. What I learned is that one of the ratios was canceling, and it wasn't a formatting issue in Excel that I caused, so the ideal mathematical way of calculating is still eluding me.

I don't have the numbers handy, but I can post them to the original drill press thread I began and cease this thread as it's pertained to Excel - which has been resolved - and confirmed it was something I was clearly doing wrong.

Smf